EXPORT TO PARQUET

Exports a table, columns from a table, or query results to files in the Parquet format.

You can use an OVER() clause to partition the data before export. You can partition data instead of or in addition to exporting the column data. Partitioning data can improve query performance by enabling partition pruning. See Improving Query Performance.

There are some limitations on the queries you can use in an export statement. See Query Restrictions.

You can export data stored in Vertica in ROS format and data from external tables.

This statement returns the number of rows written and logs information about exported files in a system table. See Monitoring Exports.

During an export to HDFS or an NFS mount point, Vertica writes files to a temporary directory in the same location as the destination and renames the directory when the export is complete. Do not attempt to use the files in the temporary directory. During an export to S3, GCS, or Azure, Vertica writes files directly to the destination path, so you must wait for the export to finish before reading the files. For more information, see Exporting to Object Stores.

After you export data, you can use the GET_METADATA function to inspect the results.

Syntax

EXPORT [ /*+LABEL (label‑string)*/ ] TO PARQUET ( directory='path'[, param=value [,...] ] )
   [ OVER (over‑clause ) ] AS SELECT query‑expression

Parameters

Arguments

Privileges

Non-superusers:

• Source table: SELECT
• Source table schema: USAGE
• Destination directory: Write

Query Restrictions

You must provide an alias column label for selected column targets that are expressions.

The query can contain only a single outer SELECT statement. For example, you cannot use UNION as in the following example.

=> EXPORT TO PARQUET(directory = '/mnt/shared_nfs/accounts/rm') 
   OVER(PARTITION BY hash) 
   AS
   SELECT 1 as account_id, '{}' as json, 0 hash
   UNION ALL
   SELECT 2 as account_id, '{}' as json, 1 hash;
ERROR 8975:  Only a single outer SELECT statement is supported
HINT:  Please use a subquery for multiple outer SELECT statements

Instead, rewrite the query to use a subquery:

=> EXPORT TO PARQUET(directory = '/mnt/shared_nfs/accounts/rm')
   OVER(PARTITION BY hash)
   AS
   SELECT
    account_id,
    json
   FROM
   (
     SELECT 1 as account_id, '{}' as json, 0 hash
     UNION ALL
     SELECT 2 as account_id, '{}' as json, 1 hash
   ) a; 
 Rows Exported 
---------------
             2
(1 row)			

To use composite statements such as UNION, INTERSECT, and EXCEPT, rewrite them as subqueries.

Data Types

EXPORT TO PARQUET converts Vertica data types to Hive data types as shown in the following table.

Decimal precision must be <= 38.

The exported Hive types might not be identical to the Vertica types. For example, a Vertica INT is exported as a Hive BIGINT. When defining Hive external tables to read exported data, you might have to adjust column definitions.

This operation exports raw Flex columns as binary data.

You cannot export data from an external table that contains external, non-native data types, such as ROW. See Complex Types. This restriction applies even if your SELECT statement does not include those columns.

Output

The export operation always creates an output directory, even if the query produces zero rows.

Output file names follow the pattern: [8-character-hash]-[nodename]-[thread-id].parquet.

Column names in partition directories are lowercase.

Files exported to a local file system by any Vertica user are owned by the Vertica superuser. Files exported to HDFS or object stores are owned by the Vertica user who exported the data.

The following requirements and restrictions apply:

• Avoid concurrent exports to the same output destination. Doing so is an error on any file system and can produce incorrect results.
• Use a shared file location for output. If you use a directory in the local file system, it must be an NFS-mounted directory.
• For output to the local file system, you must have a USER storage location.
• When exporting to the local file system, the permission mode is 700 for directories and 600 for files. You cannot override these values.

For restrictions specific to object stores (S3, GCS, or Azure), see Exporting to Object Stores.

Examples

The following example demonstrates exporting all columns from the T1 table in the public schema, using GZIP compression.

=> EXPORT TO PARQUET(directory='webhdfs:///user1/data', compression='gzip') 
  AS SELECT * FROM public.T1;

The following example demonstrates exporting the results of a query using more than one table.

=> EXPORT TO PARQUET(directory='s3://DataLake/sales_by_region') 
   AS SELECT sale.price, sale.date, store.region 
   FROM public.sales sale 
   JOIN public.vendor store ON sale.distribID = store.ID;

The following example demonstrates partitioning and exporting data. EXPORT TO PARQUET first partitions the data on region and then, within each partition, sorts by store.

=> EXPORT TO PARQUET(directory='gs://DataLake/user2/data') 
  OVER(PARTITION BY store.region ORDER BY store.ID) 
  AS SELECT sale.price, sale.date, store.ID 
  FROM public.sales sale
  JOIN public.vendor store ON sale.distribID = store.ID;

The following example uses an alias column label for a selected column target that is an expression.

=> EXPORT TO PARQUET(directory='webhdfs:///user3/data') 
  OVER(ORDER BY col1) AS SELECT col1 + col1 AS A, col2
  FROM public.T3;

The following example sets permissions for the output.

=> EXPORT TO PARQUET(directory='webhdfs:///user1/data',
     fileMode='432', dirMode='rwxrw-r-x') 
  AS SELECT * FROM public.T1;