Версия: 1.5.x

FILES

Описание

Определяет файлы данных в удаленном хранилище. Может использоваться для:

Загрузки или запроса данных из удаленной системы хранения
Выгрузки данных в удаленную систему хранения

В настоящее время функция FILES() поддерживает следующие источники данных и форматы файлов:

Источники данных:
- HDFS
- AWS S3
- Google Cloud Storage
- Другие S3-совместимые системы хранения
- Microsoft Azure Blob Storage
- NFS(NAS)
Форматы файлов:
- Parquet
- ORC
- CSV

Начиная с версии 1.5.0, FILES() дополнительно поддерживает сложные типы данных, включая ARRAY, JSON, MAP и STRUCT, помимо базовых типов данных.

FILES() для загрузки

Начиная с версии 1.5.0, Selena поддерживает определение файлов только для чтения в удаленном хранилище с помощью табличной функции FILES(). Она может получать доступ к удаленному хранилищу с помощью свойств путей файлов, определять схему таблицы данных в файлах и возвращать строки данных. Вы можете напрямую запрашивать строки данных с помощью SELECT, загружать строки данных в существующую таблицу с помощью INSERT или создавать новую таблицу и загружать в неё строки данных с помощью CREATE TABLE AS SELECT. Начиная с версии 1.5.0, вы также можете просматривать схему файла данных с помощью FILES() и DESC.

Синтаксис

FILES( data_location , data_format [, schema_detect ] [, StorageCredentialParams ] [, columns_from_path ] )

Параметры

Все параметры указываются в парах "ключ" = "значение".

data_location

URI, используемый для доступа к файлам.

Вы можете указать путь или файл. Например, вы можете указать этот параметр как "hdfs://<hdfs_host>:<hdfs_port>/user/data/tablename/20210411" для загрузки файла данных с именем 20210411 из пути /user/data/tablename на сервере HDFS.

Вы также можете указать этот параметр как путь сохранения нескольких файлов данных, используя подстановочные знаки ?, *, [], {} или ^. Например, вы можете указать этот параметр как "hdfs://<hdfs_host>:<hdfs_port>/user/data/tablename/*/*" или "hdfs://<hdfs_host>:<hdfs_port>/user/data/tablename/dt=202104*/*" для загрузки файлов данных из всех разделов или только разделов 202104 в пути /user/data/tablename на сервере HDFS.

примечание

Подстановочные знаки также могут использоваться для указания промежуточных путей.

Для доступа к HDFS необходимо указать этот параметр как:

"path" = "hdfs://<hdfs_host>:<hdfs_port>/<hdfs_path>"
-- Пример: "path" = "hdfs://127.0.0.1:9000/path/file.parquet"

Для доступа к AWS S3:
- Если вы используете протокол S3, необходимо указать этот параметр как:
```
"path" = "s3://<s3_path>"
-- Пример: "path" = "s3://path/file.parquet"
```
- Если вы используете протокол S3A, необходимо указать этот параметр как:
```
"path" = "s3a://<s3_path>"
-- Пример: "path" = "s3a://path/file.parquet"
```
Для доступа к Google Cloud Storage необходимо указать этот параметр как:
```
"path" = "s3a://<gcs_path>"
-- Пример: "path" = "s3a://path/file.parquet"
```
Для доступа к Azure Blob Storage:
- Если ваша учетная запись хранения разрешает доступ по HTTP, необходимо указать этот параметр как:
```
"path" = "wasb://<container>@<storage_account>.blob.core.windows.net/<blob_path>"
-- Пример: "path" = "wasb://testcontainer@testaccount.blob.core.windows.net/path/file.parquet"
```
- Если ваша учетная запись хранения разрешает доступ по HTTPS, необходимо указать этот параметр как:
```
"path" = "wasbs://<container>@<storage_account>.blob.core.windows.net/<blob_path>"
-- Пример: "path" = "wasbs://testcontainer@testaccount.blob.core.windows.net/path/file.parquet"
```
Для доступа к NFS(NAS):
```
"path" = "file:///<absolute_path>"
-- Пример: "path" = "file:///home/ubuntu/parquetfile/file.parquet"
```
примечание
Для доступа к файлам в NFS через протокол file:// необходимо смонтировать устройство NAS как NFS в одном и том же каталоге каждого узла BE или CN.

data_format

Формат файла данных. Допустимые значения: parquet, orc и csv.

Необходимо установить подробные параметры для конкретных форматов файлов данных.

Parquet

Пример формата Parquet:

"format"="parquet",
"parquet.use_legacy_encoding" = "true"   -- только для выгрузки

parquet.use_legacy_encoding

Управляет техникой кодирования, используемой для типов данных DATETIME и DECIMAL. Допустимые значения: true и false (по умолчанию). Это свойство поддерживается только для выгрузки данных.

Если этот параметр установлен в true:

Для типа DATETIME система использует кодирование INT96.
Для типа DECIMAL система использует кодирование fixed_len_byte_array.

Если этот параметр установлен в false:

Для типа DATETIME система использует кодирование INT64.
Для типа DECIMAL система использует кодирование INT32 или INT64.

примечание

Для типа данных DECIMAL 128 доступно только кодирование fixed_len_byte_array. parquet.use_legacy_encoding не действует.

CSV

Пример для формата CSV:

"format"="csv",
"csv.column_separator"="\\t",
"csv.enclose"='"',
"csv.skip_header"="1",
"csv.escape"="\\"

csv.column_separator

Указывает разделитель столбцов, используемый когда файл данных в формате CSV. Если вы не указываете этот параметр, он по умолчанию равен \\t, что означает табуляцию. Разделитель столбцов, который вы указываете с помощью этого параметра, должен быть таким же, как разделитель столбцов, который фактически используется в файле данных. В противном случае задача загрузки завершится неудачей из-за неадекватного качества данных.

Задачи, использующие Files(), отправляются в соответствии с протоколом MySQL. Selena и MySQL экранируют символы в запросах загрузки. Поэтому, если разделитель столбцов является невидимым символом, таким как табуляция, необходимо добавить обратную косую черту (\) перед разделителем столбцов. Например, необходимо ввести \\t, если разделитель столбцов \t, и необходимо ввести \\n, если разделитель столбцов \n. Файлы Apache Hive™ используют \x01 в качестве разделителя столбцов, поэтому необходимо ввести \\x01, если файл данных из Hive.

ПРИМЕЧАНИЕ

Для данных CSV вы можете использовать строку UTF-8, такую как запятая (,), табуляция или вертикальная черта (|), длина которой не превышает 50 байт, в качестве текстового разделителя.

Нулевые значения обозначаются с помощью \N. Например, файл данных состоит из трех столбцов, и запись из этого файла данных содержит данные в первом и третьем столбцах, но не содержит данных во втором столбце. В этой ситуации необходимо использовать \N во втором столбце для обозначения нулевого значения. Это означает, что запись должна быть составлена как a,\N,b вместо a,,b. a,,b означает, что второй столбец записи содержит пустую строку.

csv.enclose

Указывает символ, который используется для обертывания значений полей в файле данных в соответствии с RFC4180, когда файл данных в формате CSV. Тип: однобайтовый символ. Значение по умолчанию: NONE. Наиболее распространенными символами являются одинарная кавычка (') и двойная кавычка (").

Все специальные символы (включая разделители строк и столбцов), обернутые с помощью символа, указанного в enclose, считаются обычными символами. Selena может делать больше, чем RFC4180, поскольку позволяет указать любой однобайтовый символ в качестве символа, указанного в enclose.

Если значение поля содержит символ, указанный в enclose, вы можете использовать тот же символ для экранирования этого символа, указанного в enclose. Например, вы устанавливаете enclose в ", и значение поля a "quoted" c. В этом случае вы можете ввести значение поля как "a ""quoted"" c" в файл данных.

csv.skip_header

Указывает количество строк заголовка, которые нужно пропустить в данных формата CSV. Тип: INTEGER. Значение по умолчанию: 0.

В некоторых файлах данных формата CSV несколько строк заголовка используются для определения метаданных, таких как имена столбцов и типы данных столбцов. Установив параметр skip_header, вы можете позволить Selena пропускать эти строки заголовка. Например, если вы установите этот параметр в 1, Selena пропустит первую строку файла данных во время загрузки данных.

Строки заголовка в файле данных должны быть разделены с помощью разделителя строк, который вы указываете в операторе загрузки.

csv.escape

Указывает символ, который используется для экранирования различных специальных символов, таких как разделители строк, разделители столбцов, символы экранирования и символы, указанные в enclose, которые затем считаются Selena обычными символами и анализируются как часть значений полей, в которых они находятся. Тип: однобайтовый символ. Значение по умолчанию: NONE. Наиболее распространенным символом является косая черта (\), которая должна быть записана как двойная косая черта (\\) в операторах SQL.

ПРИМЕЧАНИЕ

Символ, указанный в escape, применяется как внутри, так и снаружи каждой пары символов, указанных в enclose. Два примера следующие:

Когда вы устанавливаете enclose в " и escape в \, Selena анализирует "say \"Hello world\"" в say "Hello world".

Предположим, что разделитель столбцов - запятая (,). Когда вы устанавливаете escape в \, Selena анализирует a, b\, c в два отдельных значения поля: a и b, c.

schema_detect

Начиная с версии 1.5.0, FILES() поддерживает автоматическое обнаружение схемы и объединение одной и той же партии файлов данных. Selena сначала обнаруживает схему данных, выбирая определенные строки данных случайного файла данных в партии. Затем Selena объединяет столбцы из всех файлов данных в партии.

Вы можете настроить правило выборки, используя следующие параметры:

auto_detect_sample_files: количество случайных файлов данных для выборки в каждой партии. Диапазон: [0, + ∞]. По умолчанию: 1.
auto_detect_sample_rows: количество строк данных для сканирования в каждом выбранном файле данных. Диапазон: [0, + ∞]. По умолчанию: 500.

После выборки Selena объединяет столбцы из всех файлов данных согласно этим правилам:

Для столбцов с разными именами столбцов или индексами каждый столбец идентифицируется как отдельный столбец, и, в конечном итоге, возвращается объединение всех отдельных столбцов.
Для столбцов с одинаковым именем столбца, но разными типами данных, они идентифицируются как один и тот же столбец, но с общим типом данных на относительно тонком уровне детализации. Например, если столбец col1 в файле A имеет тип INT, но DECIMAL в файле B, в возвращаемом столбце используется DOUBLE.
- Все целочисленные столбцы будут объединены как целочисленный тип на общем более грубом уровне детализации.
- Целочисленные столбцы вместе со столбцами типа FLOAT будут объединены как тип DECIMAL.
- Строковые типы используются для объединения других типов.
Как правило, тип STRING может использоваться для объединения всех типов данных.

Вы можете обратиться к Примеру 5.

Если Selena не удается объединить все столбцы, она генерирует отчет об ошибке схемы, который включает информацию об ошибке и все схемы файлов.

ВНИМАНИЕ

Все файлы данных в одной партии должны быть одного и того же формата файла.

StorageCredentialParams

Информация аутентификации, используемая Selena для доступа к вашей системе хранения.

Selena в настоящее время поддерживает доступ к HDFS с простой аутентификацией, доступ к AWS S3 и GCS с аутентификацией на основе пользователя IAM и доступ к Azure Blob Storage с общим ключом.

Используйте простую аутентификацию для доступа к HDFS:

"hadoop.security.authentication" = "simple",
"username" = "xxxxxxxxxx",
"password" = "yyyyyyyyyy"

Ключ	Обязательный	Описание
hadoop.security.authentication	Нет	Метод аутентификации. Допустимое значение: `simple` (по умолчанию). `simple` представляет простую аутентификацию, то есть без аутентификации.
username	Да	Имя пользователя учетной записи, которую вы хотите использовать для доступа к NameNode кластера HDFS.
password	Да	Пароль учетной записи, которую вы хотите использовать для доступа к NameNode кластера HDFS.

Используйте аутентификацию на основе пользователя IAM для доступа к AWS S3:

"aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
"aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
"aws.s3.region" = "<s3_region>"

Ключ	Обязательный	Описание
aws.s3.access_key	Да	Access Key ID, который вы можете использовать для доступа к корзине Amazon S3.
aws.s3.secret_key	Да	Secret Access Key, который вы можете использовать для доступа к корзине Amazon S3.
aws.s3.region	Да	Регион, в котором находится ваша корзина AWS S3. Пример: `us-west-2`.

Используйте аутентификацию на основе пользователя IAM для доступа к GCS:

"fs.s3a.access.key" = "AAAAAAAAAAAAAAAAAAAA",
"fs.s3a.secret.key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB"
"fs.s3a.endpoint" = "<gcs_endpoint>"

Ключ	Обязательный	Описание
fs.s3a.access.key	Да	Access Key ID, который вы можете использовать для доступа к корзине GCS.
fs.s3a.secret.key	Да	Secret Access Key, который вы можете использовать для доступа к корзине GCS.
fs.s3a.endpoint	Да	Конечная точка, которую вы можете использовать для доступа к корзине GCS. Пример: `storage.googleapis.com`. Не указывайте `https` в адресе конечной точки.

Используйте общий ключ для доступа к Azure Blob Storage:

"azure.blob.storage_account" = "<storage_account>",
"azure.blob.shared_key" = "<shared_key>"

Ключ	Обязательный	Описание
azure.blob.storage_account	Да	Имя учетной записи Azure Blob Storage.
azure.blob.shared_key	Да	Общий ключ, который вы можете использовать для доступа к учетной записи Azure Blob Storage.

columns_from_path

Начиная с версии 1.5.0, Selena может извлекать значение пары ключ/значение из пути файла как значение столбца.

"columns_from_path" = "<column_name> [, ...]"

Предположим, что файл данных file1 хранится по пути в формате /geo/country=US/city=LA/. Вы можете указать параметр columns_from_path как "columns_from_path" = "country, city" для извлечения географической информации в пути файла как значения столбцов, которые возвращаются. Для дальнейших инструкций см. Пример 4.

Возвращаемое значение

SELECT FROM FILES()

При использовании с SELECT, FILES() возвращает данные в файле как таблицу.

При запросе CSV файлов вы можете использовать $1, $2 ... для представления каждого столбца в операторе SELECT или указать * для получения данных из всех столбцов.

SELECT * FROM FILES(
    "path" = "s3://inserttest/csv/file1.csv",
    "format" = "csv",
    "csv.column_separator"=",",
    "csv.row_delimiter"="\n",
    "csv.enclose"='"',
    "csv.skip_header"="1",
    "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
    "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
    "aws.s3.region" = "us-west-2"
)
WHERE $1 > 5;
+------+---------+------------+
| $1   | $2      | $3         |
+------+---------+------------+
|    6 | 0.34413 | 2017-11-25 |
|    7 | 0.40055 | 2017-11-26 |
|    8 | 0.42437 | 2017-11-27 |
|    9 | 0.67935 | 2017-11-27 |
|   10 | 0.22783 | 2017-11-29 |
+------+---------+------------+
5 rows in set (0.30 sec)

SELECT $1, $2 FROM FILES(
    "path" = "s3://inserttest/csv/file1.csv",
    "format" = "csv",
    "csv.column_separator"=",",
    "csv.row_delimiter"="\n",
    "csv.enclose"='"',
    "csv.skip_header"="1",
    "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
    "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
    "aws.s3.region" = "us-west-2"
);
+------+---------+
| $1   | $2      |
+------+---------+
|    1 | 0.71173 |
|    2 | 0.16145 |
|    3 | 0.80524 |
|    4 | 0.91852 |
|    5 | 0.37766 |
|    6 | 0.34413 |
|    7 | 0.40055 |
|    8 | 0.42437 |
|    9 | 0.67935 |
|   10 | 0.22783 |
+------+---------+
10 rows in set (0.38 sec)

При запросе файлов Parquet или ORC вы можете напрямую указать имя желаемых столбцов в операторе SELECT или указать * для получения данных из всех столбцов.

SELECT * FROM FILES(
    "path" = "s3://inserttest/parquet/file2.parquet",
    "format" = "parquet",
    "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
    "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
    "aws.s3.region" = "us-west-2"
)
WHERE c1 IN (101,105);
+------+------+---------------------+
| c1   | c2   | c3                  |
+------+------+---------------------+
|  101 |    9 | 2018-05-15T18:30:00 |
|  105 |    6 | 2018-05-15T18:30:00 |
+------+------+---------------------+
2 rows in set (0.29 sec)

SELECT c1, c3 FROM FILES(
    "path" = "s3://inserttest/parquet/file2.parquet",
    "format" = "parquet",
    "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
    "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
    "aws.s3.region" = "us-west-2"
);
+------+---------------------+
| c1   | c3                  |
+------+---------------------+
|  101 | 2018-05-15T18:30:00 |
|  102 | 2018-05-15T18:30:00 |
|  103 | 2018-05-15T18:30:00 |
|  104 | 2018-05-15T18:30:00 |
|  105 | 2018-05-15T18:30:00 |
|  106 | 2018-05-15T18:30:00 |
|  107 | 2018-05-15T18:30:00 |
|  108 | 2018-05-15T18:30:00 |
|  109 | 2018-05-15T18:30:00 |
|  110 | 2018-05-15T18:30:00 |
+------+---------------------+
10 rows in set (0.55 sec)

DESC FILES()

При использовании с DESC, FILES() возвращает схему файла.

DESC FILES(
    "path" = "s3://inserttest/lineorder.parquet",
    "format" = "parquet",
    "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
    "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
    "aws.s3.region" = "us-west-2"
);

+------------------+------------------+------+
| Field            | Type             | Null |
+------------------+------------------+------+
| lo_orderkey      | int              | YES  |
| lo_linenumber    | int              | YES  |
| lo_custkey       | int              | YES  |
| lo_partkey       | int              | YES  |
| lo_suppkey       | int              | YES  |
| lo_orderdate     | int              | YES  |
| lo_orderpriority | varchar(1048576) | YES  |
| lo_shippriority  | int              | YES  |
| lo_quantity      | int              | YES  |
| lo_extendedprice | int              | YES  |
| lo_ordtotalprice | int              | YES  |
| lo_discount      | int              | YES  |
| lo_revenue       | int              | YES  |
| lo_supplycost    | int              | YES  |
| lo_tax           | int              | YES  |
| lo_commitdate    | int              | YES  |
| lo_shipmode      | varchar(1048576) | YES  |
+------------------+------------------+------+
17 rows in set (0.05 sec)

FILES() для выгрузки

Начиная с версии 1.5.0, FILES() поддерживает запись данных в файлы в удаленном хранилище. Вы можете использовать INSERT INTO FILES() для выгрузки данных из Selena в удаленное хранилище.

Синтаксис

FILES( data_location , data_format [, StorageCredentialParams ] , unload_data_param )

Параметры

Все параметры указываются в парах "ключ" = "значение".

unload_data_param

unload_data_param ::=
    "compression" = { "uncompressed" | "gzip" | "snappy" | "zstd | "lz4" },
    "partition_by" = "<column_name> [, ...]",
    "single" = { "true" | "false" } ,
    "target_max_file_size" = "<int>"

Ключ	Обязательный	Описание
compression	Да	Метод сжатия для использования при выгрузке данных. Допустимые значения: `uncompressed`: Алгоритм сжатия не используется. `gzip`: Использовать алгоритм сжатия gzip. `snappy`: Использовать алгоритм сжатия SNAPPY. `zstd`: Использовать алгоритм сжатия Zstd. `lz4`: Использовать алгоритм сжатия LZ4. ПРИМЕЧАНИЕ Выгрузка в файлы CSV не поддерживает сжатие данных. Необходимо установить этот параметр как `uncompressed`.
partition_by	Нет	Список столбцов, которые используются для разделения файлов данных на разные пути хранения. Несколько столбцов разделяются запятыми (,). FILES() извлекает информацию ключ/значение указанных столбцов и сохраняет файлы данных по путям хранения, отличающимся извлеченной парой ключ/значение. Для дальнейших инструкций см. Пример 7.
single	Нет	Выгружать ли данные в один файл. Допустимые значения: `true`: Данные сохраняются в одном файле данных. `false` (по умолчанию): Данные сохраняются в нескольких файлах, если объем выгружаемых данных превышает 512 МБ.
target_max_file_size	Нет	Максимальный размер каждого файла в партии для выгрузки (по возможности). Единица: байты. Значение по умолчанию: 1073741824 (1 ГБ). Когда размер данных для выгрузки превышает это значение, данные будут разделены на несколько файлов, и размер каждого файла не будет значительно превышать это значение. Введено в версии 3.2.7.

Примеры

Пример 1: Запрос данных из файла

Запрос данных из файла Parquet parquet/par-dup.parquet в корзине AWS S3 inserttest:

SELECT * FROM FILES(
     "path" = "s3://inserttest/parquet/par-dup.parquet",
     "format" = "parquet",
     "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
     "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
     "aws.s3.region" = "us-west-2"
);
+------+---------------------------------------------------------+
| c1   | c2                                                      |
+------+---------------------------------------------------------+
|    1 | {"1": "key", "1": "1", "111": "1111", "111": "aaaa"}    |
|    2 | {"2": "key", "2": "NULL", "222": "2222", "222": "bbbb"} |
+------+---------------------------------------------------------+
2 rows in set (22.335 sec)

Запрос данных из файлов Parquet в NFS(NAS):

SELECT * FROM FILES(
  'path' = 'file:///home/ubuntu/parquetfile/*.parquet', 
  'format' = 'parquet'
);

Пример 2: Вставка строк данных из файла

Вставка строк данных из файла Parquet parquet/insert_wiki_edit_append.parquet в корзине AWS S3 inserttest в таблицу insert_wiki_edit:

INSERT INTO insert_wiki_edit
    SELECT * FROM FILES(
        "path" = "s3://inserttest/parquet/insert_wiki_edit_append.parquet",
        "format" = "parquet",
        "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
        "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
        "aws.s3.region" = "us-west-2"
);
Query OK, 2 rows affected (23.03 sec)
{'label':'insert_d8d4b2ee-ac5c-11ed-a2cf-4e1110a8f63b', 'status':'VISIBLE', 'txnId':'2440'}

Вставка строк данных из файлов CSV в NFS(NAS) в таблицу insert_wiki_edit:

INSERT INTO insert_wiki_edit
  SELECT * FROM FILES(
    'path' = 'file:///home/ubuntu/csvfile/*.csv', 
    'format' = 'csv', 
    'csv.column_separator' = ',', 
    'csv.row_delimiter' = '\n'
  );

Пример 3: CTAS со строками данных из файла

Создание таблицы с именем ctas_wiki_edit и вставка строк данных из файла Parquet parquet/insert_wiki_edit_append.parquet в корзине AWS S3 inserttest в таблицу:

CREATE TABLE ctas_wiki_edit AS
    SELECT * FROM FILES(
        "path" = "s3://inserttest/parquet/insert_wiki_edit_append.parquet",
        "format" = "parquet",
        "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
        "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
        "aws.s3.region" = "us-west-2"
);
Query OK, 2 rows affected (22.09 sec)
{'label':'insert_1a217d70-2f52-11ee-9e4a-7a563fb695da', 'status':'VISIBLE', 'txnId':'3248'}

Пример 4: Запрос данных из файла и извлечение информации ключ/значение из его пути

Запрос данных из файла Parquet /geo/country=US/city=LA/file1.parquet (который содержит только два столбца - id и user) и извлечение информации ключ/значение из его пути как возвращаемых столбцов.

SELECT * FROM FILES(
    "path" = "hdfs://xxx.xx.xxx.xx:9000/geo/country=US/city=LA/file1.parquet",
    "format" = "parquet",
    "hadoop.security.authentication" = "simple",
    "username" = "xxxxx",
    "password" = "xxxxx",
    "columns_from_path" = "country, city"
);
+------+---------+---------+------+
| id   | user    | country | city |
+------+---------+---------+------+
|    1 | richard | US      | LA   |
|    2 | amber   | US      | LA   |
+------+---------+---------+------+
2 rows in set (3.84 sec)

Пример 5: Автоматическое обнаружение схемы и объединение

Следующий пример основан на двух файлах Parquet в корзине S3:

Файл 1 содержит три столбца - столбец INT c1, столбец FLOAT c2 и столбец DATE c3.

c1,c2,c3
1,0.71173,2017-11-20
2,0.16145,2017-11-21
3,0.80524,2017-11-22
4,0.91852,2017-11-23
5,0.37766,2017-11-24
6,0.34413,2017-11-25
7,0.40055,2017-11-26
8,0.42437,2017-11-27
9,0.67935,2017-11-27
10,0.22783,2017-11-29

Файл 2 содержит три столбца - столбец INT c1, столбец INT c2 и столбец DATETIME c3.

c1,c2,c3
101,9,2018-05-15T18:30:00
102,3,2018-05-15T18:30:00
103,2,2018-05-15T18:30:00
104,3,2018-05-15T18:30:00
105,6,2018-05-15T18:30:00
106,1,2018-05-15T18:30:00
107,8,2018-05-15T18:30:00
108,5,2018-05-15T18:30:00
109,6,2018-05-15T18:30:00
110,8,2018-05-15T18:30:00

Используйте оператор CTAS для создания таблицы с именем test_ctas_parquet и вставки строк данных из двух файлов Parquet в таблицу:

CREATE TABLE test_ctas_parquet AS
SELECT * FROM FILES(
    "path" = "s3://inserttest/parquet/*",
    "format" = "parquet",
    "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
    "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
    "aws.s3.region" = "us-west-2"
);

Просмотр схемы таблицы test_ctas_parquet:

SHOW CREATE TABLE test_ctas_parquet\G

*************************** 1. row ***************************
       Table: test_ctas_parquet
Create Table: CREATE TABLE `test_ctas_parquet` (
  `c1` bigint(20) NULL COMMENT "",
  `c2` decimal(38, 9) NULL COMMENT "",
  `c3` varchar(1048576) NULL COMMENT ""
) ENGINE=OLAP 
DUPLICATE KEY(`c1`, `c2`)
COMMENT "OLAP"
DISTRIBUTED BY RANDOM
PROPERTIES (
"bucket_size" = "4294967296",
"compression" = "LZ4",
"replication_num" = "3"
);

Результат показывает, что столбец c2, который содержит данные как FLOAT, так и INT, объединен как столбец DECIMAL, а c3, который содержит данные как DATE, так и DATETIME, объединен как столбец VARCHAR.

Приведенный выше результат остается тем же, когда файлы Parquet заменяются файлами CSV, содержащими те же данные:

CREATE TABLE test_ctas_csv AS
  SELECT * FROM FILES(
    "path" = "s3://inserttest/csv/*",
    "format" = "csv",
    "csv.column_separator"=",",
    "csv.row_delimiter"="\n",
    "csv.enclose"='"',
    "csv.skip_header"="1",
    "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
    "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
    "aws.s3.region" = "us-west-2"
);
Query OK, 0 rows affected (30.90 sec)

SHOW CREATE TABLE test_ctas_csv\G
*************************** 1. row ***************************
       Table: test_ctas_csv
Create Table: CREATE TABLE `test_ctas_csv` (
  `c1` bigint(20) NULL COMMENT "",
  `c2` decimal(38, 9) NULL COMMENT "",
  `c3` varchar(1048576) NULL COMMENT ""
) ENGINE=OLAP 
DUPLICATE KEY(`c1`, `c2`)
COMMENT "OLAP"
DISTRIBUTED BY RANDOM
PROPERTIES (
"bucket_size" = "4294967296",
"compression" = "LZ4",
"replication_num" = "3"
);
1 row in set (0.27 sec)

Пример 6: Просмотр схемы файла

Просмотр схемы файла Parquet lineorder, хранящегося в AWS S3, с помощью DESC.

DESC FILES(
    "path" = "s3://inserttest/lineorder.parquet",
    "format" = "parquet",
    "aws.s3.access_key" = "AAAAAAAAAAAAAAAAAAAA",
    "aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB",
    "aws.s3.region" = "us-west-2"
);

+------------------+------------------+------+
| Field            | Type             | Null |
+------------------+------------------+------+
| lo_orderkey      | int              | YES  |
| lo_linenumber    | int              | YES  |
| lo_custkey       | int              | YES  |
| lo_partkey       | int              | YES  |
| lo_suppkey       | int              | YES  |
| lo_orderdate     | int              | YES  |
| lo_orderpriority | varchar(1048576) | YES  |
| lo_shippriority  | int              | YES  |
| lo_quantity      | int              | YES  |
| lo_extendedprice | int              | YES  |
| lo_ordtotalprice | int              | YES  |
| lo_discount      | int              | YES  |
| lo_revenue       | int              | YES  |
| lo_supplycost    | int              | YES  |
| lo_tax           | int              | YES  |
| lo_commitdate    | int              | YES  |
| lo_shipmode      | varchar(1048576) | YES  |
+------------------+------------------+------+
17 rows in set (0.05 sec)

Пример 7: Выгрузка данных

Выгрузка всех строк данных в sales_records как нескольких файлов Parquet по пути /unload/partitioned/ в кластере HDFS. Эти файлы сохраняются в разных подпутях, различающихся значениями в столбце sales_time.

INSERT INTO FILES(
    "path" = "hdfs://xxx.xx.xxx.xx:9000/unload/partitioned/",
    "format" = "parquet",
    "hadoop.security.authentication" = "simple",
    "username" = "xxxxx",
    "password" = "xxxxx",
    "compression" = "lz4",
    "partition_by" = "sales_time"
)
SELECT * FROM sales_records;

Выгрузка результатов запроса в файлы CSV и Parquet в NFS(NAS):

-- CSV
INSERT INTO FILES(
    'path' = 'file:///home/ubuntu/csvfile/', 
    'format' = 'csv', 
    'csv.column_separator' = ',', 
    'csv.row_delimitor' = '\n'
)
SELECT * FROM sales_records;

-- Parquet
INSERT INTO FILES(
    'path' = 'file:///home/ubuntu/parquetfile/',
    'format' = 'parquet'
)
SELECT * FROM sales_records;

Описание​

FILES() для загрузки​

Синтаксис​

Параметры​

data_location​

data_format​

Parquet​

parquet.use_legacy_encoding​

CSV​

csv.column_separator​

csv.enclose​

csv.skip_header​

csv.escape​

schema_detect​

StorageCredentialParams​

columns_from_path​

Возвращаемое значение​

SELECT FROM FILES()​

DESC FILES()​

FILES() для выгрузки​

Синтаксис​

Параметры​

data_location​

data_format​

StorageCredentialParams​

unload_data_param​

Примеры​

Пример 1: Запрос данных из файла​

Пример 2: Вставка строк данных из файла​

Пример 3: CTAS со строками данных из файла​

Пример 4: Запрос данных из файла и извлечение информации ключ/значение из его пути​

Пример 5: Автоматическое обнаружение схемы и объединение​

Пример 6: Просмотр схемы файла​

Пример 7: Выгрузка данных​

Описание

FILES() для загрузки

Синтаксис

Параметры

data_location

data_format

Parquet

parquet.use_legacy_encoding

CSV

csv.column_separator

csv.enclose

csv.skip_header

csv.escape

schema_detect

StorageCredentialParams

columns_from_path

Возвращаемое значение

SELECT FROM FILES()

DESC FILES()

FILES() для выгрузки

Синтаксис

Параметры

data_location

data_format

StorageCredentialParams

unload_data_param

Примеры

Пример 1: Запрос данных из файла

Пример 2: Вставка строк данных из файла

Пример 3: CTAS со строками данных из файла

Пример 4: Запрос данных из файла и извлечение информации ключ/значение из его пути

Пример 5: Автоматическое обнаружение схемы и объединение

Пример 6: Просмотр схемы файла

Пример 7: Выгрузка данных