Request a Demo
20.4. Reading Storage Data
Prev UpChapter 20. StoragesHome Next

20.4. Reading Storage Data #

20.4.1. Reading Parquet Files (read_parquet)
20.4.2. Reading CSV Files (read_csv)
20.4.3. Reading JSON Files (read_json)
20.4.4. Reading Iceberg Tables (iceberg_scan)
20.4.5. Reading Iceberg Table Metadata (iceberg_metadata)
20.4.6. Reading Iceberg Table Snapshots (iceberg_snapshots)
20.4.7. Reading Delta Datasets (delta_scan)

20.4.1. Reading Parquet Files (read_parquet) #

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 18.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT * FROM read_parquet('path', optional_parameters);

Where:

  • path: The path to the storage directory with the Parquet files to read.

    You can specify a value in one of the following formats:

    • a path to a single Parquet file, such as 'file.parquet'

    • a glob pattern, such as 'data/*.parquet'

    • an array of paths, such as ['file1.parquet', 'file2.parquet']

  • optional_parameters: The optional parameters, specified in the parameter := value format.

    Available parameters:

    • binary_as_string: Specifies whether to return binary columns as strings.

      Possible values:

      • true

      • false

      Default value: false.

    • encryption_config: The Parquet encryption configuration, specified as a STRUCT value.

    • filename: Specifies whether to return the filename column with file paths. Since DuckDB v1.3.0, this column is added automatically.

      Possible values:

      • true

      • false

      Default value: false.

    • file_row_number: Specifies whether to return the file_row_number column with row numbers of Parquet files.

      Possible values:

      • true

      • false

      Default value: false.

    • hive_partitioning: Specifies whether the directory is associated with a partitioned analytical table and whether partition columns are returned.

      Possible values:

      • true

      • false

      Default value: Detected automatically based on the directory structure.

      For more information about Hive partitioning, refer to Chapter 27.

    • hive_types: The data types of partition columns, specified as a STRUCT value.

      If the hive_types_autocast parameter is set to true, the DATE, TIMESTAMP, and BIGINT data types are detected automatically.

      Default value: ''.

      For more information about Hive partitioning, refer to Chapter 27.

    • hive_types_autocast: Specifies whether to detect the DATE, TIMESTAMP, and BIGINT data types of partition columns automatically.

      Possible values:

      • true

      • false

      You can specify data types of partition columns manually using the hive_types parameter.

      Default value: true.

    • union_by_name: Specifies whether columns from different files are aligned by name instead of by position.

      Possible values:

      • true

      • false

      If this parameter is set to true, memory consumption increases.

      Default value: false.

    • schema: The custom schema for reading Parquet files, specified as a MAP value.

    For more information about these parameters, refer to the official DuckDB documentation on the read_parquet function.

Postgres Pro AXE returns rows of Parquet files.

Example 20.4. Executing the read_parquet Stored Procedure

  SELECT * FROM read_parquet('file.parquet', binary_as_string := true);

  id |  name   | age
  ----+---------+-----
    1 | Alice   |  30
    2 | Bob     |  25
    3 | Charlie |  35

You can select specific columns by assigning an alias to a procedure, such as r, and using the r['column_name'] syntax: 

  SELECT r['id'], r['name'] FROM read_parquet('file.parquet', binary_as_string := true) r WHERE r['age'] = 25;

id | name
----+------
  2 | Bob

20.4.2. Reading CSV Files (read_csv) #

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 18.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT * FROM read_csv('path', optional_parameters);

Where:

  • path: The path to the storage directory with the CSV files to read.

    You can specify a value in one of the following formats:

    • a path to a single CSV file, such as 'file.csv'

    • a glob pattern, such as 'data/*.csv'

    • an array of paths, such as ['file1.csv', 'file2.csv']

  • optional_parameters: The optional parameters, specified in the parameter := value format.

    Available parameters:

    • all_varchar: Specifies whether to skip automatic detection of column data types and return all columns as VARCHAR.

      Possible values:

      • true

      • false

      Default value: false.

    • allow_quoted_nulls: Specifies whether to convert quoted values to NULL values.

      Possible values:

      • true

      • false

      Default value: true.

    • auto_detect: Specifies whether to automatically detect CSV parameters, such as column data types.

      Possible values:

      • true

      • false

      Default value: true.

    • auto_type_candidates: The data types to match against when automatically detecting column data types, specified as an array. If no type matches, the VARCHAR type is used.

    • buffer_size: The size of read buffers, in bytes. It must be large enough to store four lines.

      Default value: 16 × max_line_size.

    • comment: The character used to add comments. Lines starting with this character are ignored.

      Default value: ''.

    • compression: The file compression method.

      Possible values:

      • auto_detect

      • none

      • gzip

      • zstd

      Default value: auto_detect — the compression method is detected automatically using file extensions.

    • dateformat: The format used when parsing dates.

      Default value: ''.

    • decimal_separator: The decimal separator character for numeric values.

      Default value: ..

    • delim / sep: The separator character for columns. It can be up to four bytes.

      Default value: ,.

    • escape: The character used to escape quotes within quoted values.

      Default value: ".

    • encoding: The file encoding method.

      Possible values:

      • utf-8

      • utf-16

      • latin-1

      Default value: utf-8.

    • filename: Specifies whether to return the filename column with file paths. Since DuckDB v1.3.0, this column is added automatically.

      Possible values:

      • true

      • false

      Default value: false.

    • force_not_null: The columns for which NULL matching is disabled, so their NULL values are returned as strings.

      Default value: [] — NULL matching is enabled for all columns.

    • header: Specifies whether the first line of each CSV file contains column names.

      Possible values:

      • true

      • false

      Default value: false.

    • hive_partitioning: Specifies whether the directory is associated with a partitioned analytical table and whether partition columns are returned.

      Possible values:

      • true

      • false

      Default value: Detected automatically based on the directory structure.

      For more information about Hive partitioning, refer to Chapter 27.

    • ignore_errors: Specifies whether to ignore parsing errors.

      Possible values:

      • true

      • false

      Default value: false.

    • max_line_size / maximum_line_size: The maximum line size, in bytes.

      Default value: 2000000.

    • names / column_names: The column names used when CSV files do not contain headers, specified as an array.

      Default value: '' — column names are detected automatically.

    • new_line: The character for new lines.

      Possible values:

      • \r

      • \n

      • \r\n

      Default value: ''.

    • normalize_names: Specifies whether to normalize column names by removing non-alphanumeric characters. Column names that match SQL reserved keywords are prefixed with an underscore (_).

      Possible values:

      • true

      • false

      Default value: false.

    • null_padding: Specifies whether to fill the missing columns with NULL values when a line has fewer columns than expected.

      Possible values:

      • true

      • false

      Default value: false.

    • nullstr / null: The strings to return as NULL values, specified as an array.

      Default value: ''.

    • parallel: Specifies whether to use the parallel CSV reader.

      Possible values:

      • true

      • false

      Default value: true.

    • quote: The character used to quote values.

      Default value: ".

    • rejects_scan: The name of the temporary table where information about file-level parsing errors is stored.

      Default value: reject_scans.

    • rejects_table: The name of the temporary table where information about line-level parsing errors is stored.

      Default value: reject_errors.

    • rejects_limit: The maximum number of line-level parsing errors per file to store.

      Default value: 0 — all line-level parsing errors are stored.

    • sample_size: The number of lines sampled within each CSV file for automatic detection of CSV parameters.

      Default value: 20480.

    • skip: The number of lines to skip at the start of each CSV file.

      Default value: 0.

    • store_rejects: Specifies whether to skip lines with parsing errors and store information about them in temporary tables specified by the rejects_scan and rejects_table parameters.

      Possible values:

      • true

      • false

      Default value: false.

    • strict_mode: Specifies whether to enforce the strictness level of the CSV reader.

      Possible values:

      • true

      • false

      If this parameter is set to false, the CSV reader attempts to process structurally incorrect files, which may produce unexpected results.

      Default value: true.

    • thousands: The character used as the thousands separator in numeric values. It must differ from the decimal_separator value.

      Default value: ''.

    • timestampformat: The format used when parsing timestamps.

      Default value: ''.

    • types / dtypes / column_types: The column data types to use instead of the automatically detected ones.

      You can specify a value in one of the following formats:

      • an array of type names matching the column order, such as ['INTEGER', 'VARCHAR']

      • a STRUCT value with column names as keys, such as {'col1': 'INTEGER'}

      Default value: '' — column types are detected automatically.

    • union_by_name: Specifies whether columns from different files are aligned by name instead of by position.

      Possible values:

      • true

      • false

      If this parameter is set to true, memory consumption increases.

      Default value: false.

    For more information about these parameters, refer to the official DuckDB documentation on the read_csv function.

Postgres Pro AXE returns rows of CSV files.

Example 20.5. Executing the read_csv Stored Procedure

Contents of file.csv: 

  1;Alice;30;2020-01-15
  2;Bob;25;2021-03-22
  3;Charlie;35;2019-07-01

  SELECT * FROM read_csv('file.csv',
      delim   := ';',
      header  := false,
      names   := ARRAY['id', 'name', 'age', 'hired'],
      types   := ARRAY['INTEGER', 'VARCHAR', 'INTEGER', 'DATE']);

   id |  name   | age |   hired
  ----+---------+-----+------------
    1 | Alice   |  30 | 2020-01-15
    2 | Bob     |  25 | 2021-03-22
    3 | Charlie |  35 | 2019-07-01

You can select specific columns by assigning an alias to a procedure, such as r, and using the r['column_name'] syntax: 

  SELECT r['id'], r['name'] FROM read_csv('file.csv',
      delim   := ';',
      header  := false,
      names   := ARRAY['id', 'name', 'age', 'hired'],
      types   := ARRAY['INTEGER', 'VARCHAR', 'INTEGER', 'DATE']) r
  WHERE r['age'] = 25;

 id | name
----+------
  2 | Bob

20.4.3. Reading JSON Files (read_json) #

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 18.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT * FROM read_json('path', optional_parameters);

Where:

  • path: The path to the storage directory with the JSON files to read.

    You can specify a value in one of the following formats:

    • a path to a single JSON file, such as 'file.json'

    • a glob pattern, such as 'data/*.json'

    • an array of paths, such as ['file1.json', 'file2.json']

  • optional_parameters: The optional parameters, specified in the parameter := value format.

    Available parameters:

    • compression: The file compression method.

      Possible values:

      • auto_detect

      • none

      • gzip

      • zstd

      Default value: auto_detect — the compression method is detected automatically using file extensions.

    • filename: Specifies whether to return the filename column with file paths. Since DuckDB v1.3.0, this column is added automatically.

      Possible values:

      • true

      • false

      Default value: false.

    • format: The JSON format.

      Possible values:

      • auto

      • unstructured

      • newline_delimited

      • array

      Default value: array.

    • hive_partitioning: Specifies whether the directory is associated with a partitioned analytical table and whether partition columns are returned.

      Possible values:

      • true

      • false

      Default value: Detected automatically based on the directory structure.

      For more information about Hive partitioning, refer to Chapter 27.

    • ignore_errors: Specifies whether to ignore parsing errors.

      Possible values:

      • true

      • false

      This parameter is applicable only if the format parameter is set to newline_delimited.

      Default value: false.

    • maximum_sample_files: The maximum number of JSON files sampled for automatic detection of column names and data types.

      You can specify the number of JSON objects sampled within each file manually using the sample_size parameter.

      Default value: 32.

    • maximum_object_size: The maximum size of a JSON object, in bytes.

      Default value: 16777216.

    • auto_detect: Specifies whether to automatically detect column names and data types.

      Possible values:

      • true

      • false

      Default value: true.

    • dateformat: The format used when parsing dates.

      Default value: iso.

    • maximum_depth: The maximum nesting depth for automatic detection of column names and data types.

      Default value: -1 — no nesting depth limit.

    • records: Specifies whether to return JSON objects as individual columns.

      Possible values:

      • auto

      • true

      • false

      If this parameter is set to false, each JSON object is returned as a STRUCT value.

      Default value: auto.

    • sample_size: The number of JSON objects sampled within each file for automatic detection of column names and data types.

      You can specify the number of sampled JSON files manually using the maximum_sample_files parameter.

      To scan JSON files entirely, set this parameter to -1.

      Default value: 20480.

    • timestampformat: The format used when parsing timestamps.

      Default value: iso.

    • union_by_name: Specifies whether columns from different files are aligned by name instead of by position.

      Possible values:

      • true

      • false

      If this parameter is set to true, memory consumption increases.

      Default value: false.

    • map_inference_threshold: The threshold at which automatic detection of column names and data types assigns the MAP type instead of the STRUCT type to a JSON object.

      To disable automatic assignment of the MAP type, set this parameter to -1.

      Default value: 200.

    • field_appearance_threshold: The threshold that determines whether the JSON reader assigns the MAP or the STRUCT data type to a JSON object.

      For each field, the JSON reader calculates how frequently the field appears relative to the number of objects in the sample. If the average of these ratios across all fields of an object is below this threshold, the reader defaults to assigning the MAP data type to this object.

      Default value: 0.1.

    For more information about these parameters, refer to the official DuckDB documentation on the read_json function.

Postgres Pro AXE returns rows of JSON files.

Example 20.6. Executing the read_json Stored Procedure

Contents of file.json: 

  [
    {"id": 1, "name": "Alice", "age": 30, "hired": "2020-01-15"},
    {"id": 2, "name": "Bob", "age": 25, "hired": "2021-03-22"},
    {"id": 3, "name": "Charlie", "age": 35, "hired": "2019-07-01"}
  ]

  SELECT * FROM read_json('file.json');

 id |  name   | age |   hired
----+---------+-----+------------
  1 | Alice   | 30  | 2020-01-15
  2 | Bob     | 25  | 2021-03-22
  3 | Charlie | 35  | 2019-07-01

You can select specific columns by assigning an alias to a procedure, such as r, and using the r['column_name'] syntax: 

  SELECT r['id']::INTEGER    AS id,
         r['name']::VARCHAR  AS name,
         r['age']::INTEGER   AS age,
         r['hired']::DATE    AS hired
  FROM read_json('file.json') AS r
  WHERE r['age']::INTEGER = 25;

 id | name | age |   hired
----+------+-----+------------
  2 | Bob  |  25 | 2021-03-22

20.4.4. Reading Iceberg Tables (iceberg_scan) #

Before performing this instruction, install the Iceberg extension.

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 18.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT * FROM iceberg_scan('path', optional_parameters);

Where:

  • path: The path to the storage directory with the Iceberg table to read.

  • optional_parameters: The optional parameters, specified in the parameter := value format.

    Available parameters:

    • allow_moved_paths: Specifies whether to read Iceberg tables that are moved to a different directory.

      Possible values:

      • true

      • false

      Default value: false.

    • metadata_compression_codec: The compression codec to use when reading metadata files.

      Default value: '' — no compression codec is used.

    • snapshot_from_id: The ID of the snapshot associated with the Iceberg table.

      Default value: NULL — the latest snapshot is used.

    • snapshot_from_timestamp: The timestamp of the snapshot associated with the Iceberg table.

      Default value: NULL — the latest snapshot is used.

    • version: The version of the Iceberg table metadata file.

      Default value: '?' — the version is determined automatically.

    • version_name_format: The format string that determines how the version value is converted to the Iceberg table metadata file name.

      Default value: 'v%s%s.metadata.json,%s%s.metadata.json'.

    For more information about these parameters, refer to the official DuckDB documentation on the iceberg_scan function.

Postgres Pro AXE returns rows of the Iceberg table.

Example 20.7. Executing the iceberg_scan Stored Procedure

  SELECT * FROM iceberg_scan('data/iceberg/table');

   id |  name   | age
  ----+---------+-----
    1 | Alice   |  30
    2 | Bob     |  25
    3 | Charlie |  35

You can select specific columns by assigning an alias to a procedure, such as r, and using the r['column_name'] syntax: 

  SELECT r['id'], r['name'] FROM iceberg_scan('data/iceberg/table') r WHERE r['age'] = 25;

 id | name
----+------
  2 | Bob

20.4.5. Reading Iceberg Table Metadata (iceberg_metadata) #

Before performing this instruction, install the Iceberg extension.

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 18.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT * FROM iceberg_metadata('path', optional_parameters);

Where:

  • path: The path to the storage directory with the Iceberg table to read the metadata from.

  • optional_parameters: The optional parameters, specified in the parameter := value format.

    Available parameters:

    • allow_moved_paths: Specifies whether to read Iceberg tables that are moved to a different directory.

      Possible values:

      • true

      • false

      Default value: false.

    • metadata_compression_codec: The compression codec to use when reading metadata files.

      Default value: '' — no compression codec is used.

    • snapshot_from_id: The ID of the snapshot associated with the Iceberg table.

      Default value: NULL — the latest snapshot is used.

    • snapshot_from_timestamp: The timestamp of the snapshot associated with the Iceberg table.

      Default value: NULL — the latest snapshot is used.

    • version: The version of the Iceberg table metadata file.

      Default value: '?' — the version is determined automatically.

    • version_name_format: The format string that determines how the version value is converted to the Iceberg table metadata file name.

      Default value: 'v%s%s.metadata.json,%s%s.metadata.json'.

    For more information about these parameters, refer to the official DuckDB documentation on the iceberg_metadata function.

Postgres Pro AXE returns rows with the metadata of the Iceberg table.

Example 20.8. Executing the iceberg_metadata Stored Procedure

  SELECT * FROM iceberg_metadata('data/iceberg/table');

20.4.6. Reading Iceberg Table Snapshots (iceberg_snapshots) #

Before performing this instruction, install the Iceberg extension.

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 18.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT * FROM iceberg_snapshots('path', optional_parameters);

Where:

  • path: The path to the storage directory with the Iceberg table to read the snapshots from.

  • optional_parameters: The optional parameters, specified in the parameter := value format.

    Available parameters:

    • metadata_compression_codec: The compression codec to use when reading metadata files.

      Default value: '' — no compression codec is used.

    • version: The version of the Iceberg table metadata file.

      Default value: '?' — the version is determined automatically.

    • version_name_format: The format string that determines how the version value is converted to the Iceberg table metadata file name.

      Default value: 'v%s%s.metadata.json,%s%s.metadata.json'.

    For more information about these parameters, refer to the official DuckDB documentation on the iceberg_snapshots function.

Postgres Pro AXE returns rows with snapshots of the Iceberg table.

Example 20.9. Executing the iceberg_snapshots Stored Procedure

  SELECT * FROM iceberg_snapshots('data/iceberg/table');

20.4.7. Reading Delta Datasets (delta_scan) #

Before performing this instruction, install the Delta extension.

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 18.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT * FROM delta_scan('path', optional_parameters);

Where:

  • path: The path to the storage directory with the Delta dataset to read.

  • optional_parameters: The optional parameters, specified in the parameter := value format.

    Available parameters:

    • delta_file_number: Specifies whether to return the delta_file_number column with the index of the physical Parquet file for each row.

      Possible values:

      • true

      • false

      Default value: false.

    • binary_as_string: Specifies whether to return binary columns as strings.

      Possible values:

      • true

      • false

      Default value: false.

    • filename: Specifies whether to return the filename column with file paths. Since DuckDB v1.3.0, this column is added automatically.

      Possible values:

      • true

      • false

      Default value: false.

    • file_row_number: Specifies whether to return the file_row_number column with row numbers of Parquet files.

      Possible values:

      • true

      • false

      Default value: false.

    • hive_partitioning: Specifies whether the directory is associated with a partitioned analytical table and whether partition columns are returned.

      Possible values:

      • true

      • false

      Default value: Detected automatically based on the directory structure.

      For more information about Hive partitioning, refer to Chapter 27.

    • hive_types: The data types of partition columns, specified as a STRUCT value.

      If the hive_types_autocast parameter is set to true, the DATE, TIMESTAMP, and BIGINT data types are detected automatically.

      Default value: ''.

      For more information about Hive partitioning, refer to Chapter 27.

    • hive_types_autocast: Specifies whether to detect the DATE, TIMESTAMP, and BIGINT data types of partition columns automatically.

      Possible values:

      • true

      • false

      You can specify data types of partition columns manually using the hive_types parameter.

      Default value: true.

    • union_by_name: Specifies whether columns from different files are aligned by name instead of by position.

      Possible values:

      • true

      • false

      If this parameter is set to true, memory consumption increases.

      Default value: false.

    • schema: The custom schema for reading Parquet files, specified as a MAP value.

Postgres Pro AXE returns rows of the Delta dataset.

Example 20.10. Executing the delta_scan Stored Procedure

  SELECT * FROM delta_scan('data/delta/dataset');

   id |  name   | age
  ----+---------+-----
    1 | Alice   |  30
    2 | Bob     |  25
    3 | Charlie |  35

You can select specific columns by assigning an alias to a procedure, such as r, and using the r['column_name'] syntax: 

  SELECT r['id'], r['name'] FROM delta_scan('data/delta/dataset') r WHERE r['age'] = 25;

 id | name
----+------
  2 | Bob

Prev Up Next
20.3. Configuring the Parquet File Cache Home Chapter 21. Analytical Tables
epub pdf