Reading data from Object Storage via Query connections

Using Yandex Object Storage connections is convenient for prototyping and initial data access configuration.

Query example for reading data:

SELECT
    *
FROM
    object_storage.`*.tsv`
WITH
(
    format=tsv_with_names,
    SCHEMA
    (
        `timestamp` Uint32,
        action String
    )
);

Setting up a connectionSetting up a connection

To create a connection to Object Storage:

1. In the management console, select the folder where you want to create a connection.

2. Navigate to Yandex Query.

3. In the left-hand panel, switch to the Connections tab.

4. Click Create new.

5. Specify the connection settings:

   1. Under General parameters:

      • Name: Object Storage connection name.
      • Type: Object Storage.

   2. Under Connection type parameters:

      • Bucket auth: Select Public or Private depending on the type of read access to objects in the bucket.

        For a public bucket, specify a name in the Bucket field.
        For a private bucket:

        • Select Cloud and Folder where the data source is located.

        • Select an existing bucket or create a new one.

        • Select an existing service account or create a new one. Assign it the storage.viewer role required to access the data.

          To use the service account on your behalf, you need the iam.serviceAccounts.user role.

6. Click Create.

Data modelData model

Object Storage stores data as binary files. To read data, use the following SQL statement:

SELECT
    <expression>
FROM
    <connection>.<path>
WITH(
    FORMAT = "<data_format>",
    COMPRESSION = "<compression_format>",
    SCHEMA = (<schema_description>))
WHERE
    <filter>;

Where:

• <connection>: Storage connection name.
• <path>: Path to file(s) within the bucket. Supports the * wildcard.
• <data_format>: File data format.
• <compression_format>: File compression format.
• <data_schema>: Description of data stored in files.

Data schemaData schema

The data schema includes the following fields:

• Field name
• Field type
• Required flag

For example, the schema below describes a required field Year of type Int32:

Year Int32 NOT NULL

If a NOT NULL field is missing from the file being processed, the operation will terminate with an error. If a nullable field is missing from the file being processed, no error will occur, and the field will be set to NULL. The NULL keyword may be omitted in nullable fields.

Automatic schema inferenceAutomatic schema inference

Automatic schema inference is supported for all data formats except raw and json_as_string. It is convenient when a schema has many fields. Instead of entering them manually, you can use the WITH_INFER hint:

SELECT
    <expression>
FROM
    <connection>.<path>
WITH(
    FORMAT = "<data_format>",
    COMPRESSION = "<compression_format>",
    WITH_INFER="true")
WHERE
    <filter>;

Where:

• <connection>: Storage connection name.
• <path>: Path to file(s) within the bucket. Supports the * wildcard.
• <data_format>: File data format.
• <compression_format>: File compression format.

This query will automatically infer field names and types.

Data path formatsData path formats

Yandex Query supports the following data path formats:

Example of reading data via connectionsExample of reading data via connections

Query example for reading data from Object Storage:

SELECT
    *
FROM
    connection.`folder/filename.csv`
WITH(
    format='csv_with_names',
    SCHEMA
    (
        Year int,
        Manufacturer String,
        Model String,
        Price Double
    )
);

Where:

• connection: Object Storage connection name.
• folder/filename.csv: File path within the Object Storage bucket.
• SCHEMA: Data schema described in the file.