Export Data as Parquet to a Directory

Shows the steps to export table data from your Autonomous Database to a directory as Parquet data by specifying a query.

  1. Connect to your Autonomous Database instance.

    See Connect to Autonomous Database for more information.

  2. Create a directory.

    For example:

    CREATE DIRECTORY export_dir AS 'export_dir';
    See Create Directory in Autonomous Database for more information.
  3. Run DBMS_CLOUD.EXPORT_DATA and specify the format parameter type with the value json to export the results as Parquet files to a directory. Do not include the credential parameter when sending output to a directory.
    BEGIN
     DBMS_CLOUD.EXPORT_DATA(
        file_uri_list => 'export_dir:sales.parquet',
        format => JSON_OBJECT('type' value 'parquet'),
        query => 'SELECT * FROM sales'
     );
    END;
    /
    

    The directory name is case-sensitive when the directory name is enclosed in double quotes. For example:

    BEGIN
     DBMS_CLOUD.EXPORT_DATA(
        file_uri_list => '"export_dir":sales.parquet',
        format => JSON_OBJECT('type' value 'parquet'),
        query => 'SELECT * FROM sales'
     );
    END;
    /
    

    For detailed information about the parameters, see EXPORT_DATA Procedure.

    For detailed information about the available format parameters you can use with DBMS_CLOUD.EXPORT_DATA, see DBMS_CLOUD Package Format Options for EXPORT_DATA.

    The parameters are:

    • file_uri_list: is a comma delimited list of the export file(s). Use of wildcard and substitution characters is not supported in the file_uri_list.

    • format: specifies the required type parameter. The valid values are datapump, json, xml, csv and parquet and it also optionally defines the options you can specify for the export with the ORACLE_DATAPUMP Access Driver.

    • query: specifies a SELECT statement so that only the required data is exported. The query determines the contents of the dump file(s).

    For detailed information about the parameters, see EXPORT_DATA Procedure.

Notes for exporting with DBMS_CLOUD.EXPORT_DATA:

  • The query parameter that you supply can be an advanced query, if required, such as a query that includes joins or subqueries.

  • Specify the format parameter with the compression option to compress the output files.

  • See DBMS_CLOUD Package Oracle Data Type to Parquet Mapping for details on Oracle Type to Parquet Type mapping.

    The following types are not supported or have limitations on their support for exporting Parquet with DBMS_CLOUD.EXPORT_DATA:

    Oracle Type Notes

    BFILE

    Not supported

    BLOB

    Not supported

    DATE

    Supported with the following limitation: DATE format supports only date, month and year. Hour, minute and seconds are not supported.

    See DBMS_CLOUD Package Oracle Data Type to Parquet Mapping for details on NLS format limitations for exporting DATE to Parquet.

    INTERVAL DAY TO SECOND

    Supported and is treated as string internally

    INTERVAL YEAR TO MONTH

    Supported and is treated as string internally

    LONG

    Not supported

    LONG RAW

    Not supported

    NUMBER

    Supported with the following limitations:

    • Can have maximum precision of 38 and scale equal to less than precision.
    • If no precision and scale is provided for the column NUMBER type, by default precision of 38 and scale of 20 is used.
    • Negative scale is not supported for NUMBER types.

    Object Types

    Not supported

    TIMESTAMP

    Supported with the following limitations:

    • If there are multiple columns with different precision, highest precision will be taken.
    • TIMESTAMP WITH TIME ZONE Oracle datatype will use the timestamp only.

    See DBMS_CLOUD Package Oracle Data Type to Parquet Mapping for details on NLS format limitations for exporting TIMESTAMP to Parquet.