跳到主要内容

查询与转换

Databend 支持直接查询暂存区(Stage)中的文件,无需先将数据加载到表中。可以查询任何暂存区类型(用户、内部、外部)中的文件,或直接从对象存储和 HTTPS URL 查询。这非常适合在加载数据前后进行数据检查、验证和转换。

语法

仅查询

SELECT {
[<alias>.]<column> [, [<alias>.]<column> ...] -- 按名称查询列
| [<alias>.]$<col_position> [, [<alias>.]$<col_position> ...] -- 按位置查询列
| [<alias>.]$1[:<column>] [, [<alias>.]$1[:<column>] ...] -- 将行作为 Variant 查询
}
FROM {@<stage_name>[/<path>] | '<uri>'} -- 暂存区表函数
[( -- 暂存区表函数参数
[<connection_parameters>],
[ PATTERN => '<regex_pattern>'],
[ FILE_FORMAT => 'CSV | TSV | NDJSON | PARQUET | ORC | Avro | <custom_format_name>'],
[ FILES => ( '<file_name>' [ , '<file_name>' ... ])],
[ CASE_SENSITIVE => true | false ]
)]
[<alias>]

带转换的复制

COPY INTO [<database_name>.]<table_name> [ ( <col_name> [ , <col_name> ... ] ) ]
FROM (
SELECT {
[<alias>.]<column> [, [<alias>.]<column> ...] -- 按名称查询列
| [<alias>.]$<col_position> [, [<alias>.]$<col_position> ...] -- 按位置查询列
| [<alias>.]$1[:<column>] [, [<alias>.]$1[:<column>] ...] -- 将行作为 Variant 查询
} ]
FROM {@<stage_name>[/<path>] | '<uri>'}
)
[ FILES = ( '<file_name>' [ , '<file_name>' ] [ , ... ] ) ]
[ PATTERN = '<regex_pattern>' ]
[ FILE_FORMAT = (
FORMAT_NAME = '<your-custom-format>'
| TYPE = { CSV | TSV | NDJSON | PARQUET | ORC | AVRO } [ formatTypeOptions ]
) ]
[ copyOptions ]
注意

比较两种语法:

  • 相同的 Select 列表
  • 相同的 FROM {@<stage_name>[/<path>] | '<uri>'}
  • 不同的参数:
    • 查询使用 表函数参数,即 (<key> => <value>, ...)
    • 转换在 Copy into table 的末尾使用选项

FROM 子句

FROM 子句使用与 表函数(Table Function) 类似的语法。与普通表一样,在与其他表连接时可以使用表 别名(alias)

表函数参数:

参数描述
FILE_FORMAT文件格式类型(CSV、TSV、NDJSON、PARQUET、ORC、Avro)
PATTERN用于筛选文件的正则表达式模式
FILES要查询的文件的显式列表
CASE_SENSITIVE列名大小写敏感(仅限 Parquet)
connection_parameters外部存储连接详情

查询文件数据

SELECT 列表支持三种语法;只能使用其中一种,不能混合使用。

将行作为 Variant 查询

  • 支持的文件格式:NDJSON、AVRO、Parquet、ORC
注意

目前对于 Parquet 和 ORC,将行作为 Variant 查询按名称查询列 慢,并且这两种方法不能混合使用。

语法:

SELECT [<alias>.]$1[:<column>] [, [<alias>.]$1[:<column>]  ...] <FROM Clause>
  • 示例:SELECT $1:id, $1:name FROM ...
  • 表结构:($1: Variant)。即,具有 Variant 对象类型的单列,每个 Variant 代表一整行
  • 注意:
    • $1:column 这样的路径表达式的类型也是 Variant,在表达式中使用或加载到目标表列时,它可以自动转换为原生类型。有时,为了进行特定类型的操作(例如,CAST($1:id AS INT)),你可能希望在之前手动转换,以使语义更明确。

按名称查询列

  • 支持的文件格式:NDJSON、AVRO、Parquet、ORC
SELECT [<alias>.]<column> [, [<alias>.]<column>  ...] <FROM Clause>
  • 示例:SELECT id, name FROM ...
  • 表结构:从 Parquet 或 ORC 文件模式映射的列
  • 注意:
    • 所有文件都必须具有相同的 Parquet/ORC 模式;否则,将返回错误

按位置查询列

  • 支持的文件格式:CSV、TSV
SELECT [<alias>.]$<col_position>[, [<alias>.]$<col_position>,  ...] <FROM Clause>
  • 示例:SELECT $1, $2 FROM ...
  • 表结构:类型为 VARCHAR NULL 的列
  • 注意
    • <col_position> 从 1 开始

查询元数据

你还可以在查询中包含文件元数据,这对于跟踪数据血缘和调试非常有用:

SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, $1, <FROM Clause>
(
FILE_FORMAT => 'ndjson_query_format',
PATTERN => '.*[.]ndjson'
);

以下文件级元数据字段适用于支持的文件格式:

文件元数据类型描述
METADATA$FILENAMEVARCHAR读取行的文件路径
METADATA$FILE_ROW_NUMBERINT文件内的行号(从 0 开始)

使用场景:

  • 数据血缘:跟踪每条记录来自哪个源文件
  • 调试:按文件和行号识别有问题的记录
  • 增量处理:仅处理特定文件或文件内的范围

按文件格式分类的教程