Skip to main content

Querying Data in Staged Files

Databend allows you to directly query data in the files stored in one of the following locations without loading them into a table:

  • User stage, internal stage, or external stage.
  • Bucket or container created in your object storage, such as Amazon S3, Google Cloud Storage, and Microsoft Azure.
  • Remote server.

During this process, the schema is automatically detected, just like with the infer_schema function. This feature can be particularly useful for inspecting or viewing the contents of staged files, whether it's before or after loading data.

note

This feature is currently only available for the Parquet file format.

Syntax and Parameters​

SELECT <columns> FROM
{@<stage_name>[/<path>] | '<uri>'} [(
  [ PATTERN => '<regex_pattern>']
  [ FILE_FORMAT => '<format_name>']
  [ FILES => ( 'file_name' [ , 'file_name' ... ] ) ]
  [ ENDPOINT_URL => <'url'> ]
  [ AWS_KEY_ID => <'aws_key_id'> ]
  [ AWS_KEY_SECRET => <'aws_key_secret'> ]
  [ ACCESS_KEY_ID => <'access_key_id'> ]
  [ ACCESS_KEY_SECRET => <'access_key_secret'> ]
  [ SECRET_ACCESS_KEY => <'secret_access_key'> ]
  [ SESSION_TOKEN => <'session_token'> ]
  [ REGION => <'region'> ]
  [ ENABLE_VIRTUAL_HOST_STYLE => true|false ]
)]

FILE_FORMAT​

The file format must be one of the following:

PATTERN​

A PCRE2-based regular expression pattern string, enclosed in single quotes, specifying the file names to match. Click here to see an example. For PCRE2 syntax, see http://www.pcre.org/current/doc/html/pcre2syntax.html.

FILES​

Specifies one or a list of file names (separated by commas).

Others​

To query data files in a bucket or container, provide necessary connection information with the following parameters:

  • ENDPOINT_URL
  • AWS_KEY_ID
  • AWS_SECRET_KEY
  • ACCESS_KEY_ID
  • ACCESS_KEY_SECRET
  • SECRET_ACCESS_KEY
  • SESSION_TOKEN
  • REGION
  • ENABLE_VIRTUAL_HOST_STYLE

They are explained in Create Stage.

Examples​

Example 1: Querying Data in a Parquet File​

This example shows how to query data in a Parquet file stored in different locations. Click the tabs below to see details.

Let's assume you have a sample file named books.parquet and you have uploaded it to your user stage, an internal stage named my_internal_stage, and an external stage named my_external_stage. To upload files to a stage, use the File Upload API.

-- Query file in user stage
SELECT * FROM @~/books.parquet;

-- Query file in internal stage
SELECT * FROM @my_internal_stage/books.parquet;

-- Query file in external stage
SELECT * FROM @my_external_stage/books.parquet;

Example 2: Filtering Files with Pattern Matching​

Let's assume you have a folder containing the following Parquet files with the same schema, as well as some files of other formats. You can use the PATTERN parameter to filter all the Parquet files in the folder.

data/
  ├── 2022-01-01.parquet
  ├── 2022-01-02.parquet
  ├── 2022-01-03.parquet
  ├── 2022-01-04.parquet
  └── 2022-01-05.parquet

For example, you can query the "name" and "age" columns from all Parquet files in the folder:

SELECT name, age FROM @internal_stage/data/
(file_format => 'parquet', pattern => '.*parquet');