clickhouse表函数原创

ClickHouse 表函数应用非常广泛，可通过ClickHouse引擎查询、计算其他数据源数据。不再需要繁琐的数据库之间数据同步。同时支持数据源种类颇多，S3、MySQL、PostgreSQL、URL、kafka等；

具体可在官网查看，这里列举部分常用表函数进行示例学习。

clickhouse官网-表函数 (opens new window)

# 1. S3

语法：
select column1 , column2
from s3(path, [aws_access_key_id, aws_secret_access_key], format,structure, [compression]);

1
2
3

path — 带有文件路径的 Bucket url。在只读模式下支持以下通配符: *, ?, {abc,def} 和 {N..M} 其中 N, M 是数字, 'abc', 'def' 是字符串. 更多信息见下文.
format — 文件的格式.常用CSV、TSV、CustomSeparated(自定义分隔符)
structure — 表的结构. 格式像这样 'column1_name column1_type, column2_name column2_type, ...'.
compression — 压缩类型. 支持的值: none, gzip/gz, brotli/br, xz/LZMA, zstd/zst. 参数是可选的. 默认情况下，通过文件扩展名自动检测压缩类型.

# 1.1. 读外部表

以cos为例，如果clickhouse配置中没有添加access_key_id、secret_access_key，在表函数中添加即可，如果已经配置，则不需要添加。

示例1(CSV默认分隔符 , )

select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String');

1
2

示例2(自定义输入CSV文件分隔符：format_csv_delimiter)

select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String')
settings format_csv_delimiter='|';

1
2
3

示例3(指定允许异常跳过： input_format_allow_errors_num，input_format_allow_errors_ratio )

select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String')
settings format_csv_delimiter='|',
         input_format_allow_errors_num=500;

1
2
3
4

# 1.2. 写外部表

以cos为例

示例1(CSV默认分隔符 , )：但是导出数据带有引号如果只需要逗号分隔，可以使用自定义分隔符

INSERT INTO FUNCTION
s3('http://demo.cos.ap-shanghai.myqcloud.com/out.csv','CSV','id String, name String')
select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String');

1
2
3
4

示例2(自定义输出文件分隔符：format_custom_field_delimiter)

INSERT INTO FUNCTION
s3('http://demo.cos.ap-shanghai.myqcloud.com/out.csv','CustomSeparated','id String, name String')
select id, name
from s3('http://demo.cos.ap-shanghai.myqcloud.com/test.csv','CSV','id String, name String')
settings format_csv_delimiter='|',
         format_custom_field_delimiter='|';

1
2
3
4
5
6

# 1.3. 常用SETTINGS

clickhouse官网-SETTINGS (opens new window)

用途	函数	备注
设备CSV的分隔符	format_csv_delimiter	默认 ,
自定义输出分隔符	format_custom_field_delimiter
输入可接受错误行数	input_format_allow_errors_num	默认0
允许选择时间的文本表示	date_time_input_format	默认不允许

# 2. postgreSQL

clickhouse官网-使用文档 (opens new window)

语法：
postgresql('host:port', 'database', 'table', 'user', 'password'[, `schema`])

1
2

host:port — PostgreSQL 服务器地址.
database — 远程数据库名称.
table — 远程表名称.
user — PostgreSQL 用户.
password — 用户密码.
schema — 非默认的表结构. 可选.

示例
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');

1
2

# 3. mysql

clickhouse官网-使用文档 (opens new window)

语法：
mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

1
2

host:port — MySQL服务器地址.
database — 远程数据库名称.
table — 远程表名称.
user — MySQL用户.
password — 用户密码.

示例
SELECT * FROM mysql('localhost:3306', 'test', 'test', 'bayonet', '123')

1
2

← clickhouse DDL DML clickhouse字符串函数→