一、簡介

clickhouse有多種數據的導入導出方式，可以靈活使用，下面對這些方式分別做些介紹，導入導出的寫法與格式和格式設置有關。

二、導入

1.從s3導入

詳情可查看官網，也可以在這里獲取數據集

-- 建庫建表
CREATE DATABASE git;CREATE TABLE git.commits
(hash String,author LowCardinality(String),time DateTime,message String,files_added UInt32,files_deleted UInt32,files_renamed UInt32,files_modified UInt32,lines_added UInt32,lines_deleted UInt32,hunks_added UInt32,hunks_removed UInt32,hunks_changed UInt32
) ENGINE = MergeTree ORDER BY time;-- 導入數據
INSERT INTO git.commits SELECT *
FROM s3('https://datasets-documentation.s3.amazonaws.com/github/commits/clickhouse/commits.tsv.xz', 'TSV', 'hash String,author LowCardinality(String), time DateTime, message String, files_added UInt32, files_deleted UInt32, files_renamed UInt32, files_modified UInt32, lines_added UInt32, lines_deleted UInt32, hunks_added UInt32, hunks_removed UInt32, hunks_changed UInt32')0 rows in set. Elapsed: 1.826 sec. Processed 62.78 thousand rows, 8.50 MB (34.39 thousand rows/s., 4.66 MB/s.)

s3的表達式如下

s3(path, [aws_access_key_id, aws_secret_access_key,] [format, [structure, [compression]]])

• path — 包含文件路徑的存儲桶 URL。 這在只讀模式下支持以下通配符：*、?、{abc,def} 和 {N…M}，其中 N、M 是數字，‘abc’、‘def’ 是字符串。 有關更多信息，請參閱有關在路徑中使用通配符的文檔。
• format — 文件的格式。
• structure — 表的結構。 格式為“column1_name、column1_type、column2_name、column2_type，…”。
• compression — 參數是可選的。 支持的值：none、gzip/gz、brotli/br、xz/LZMA、zstd/zst。 默認情況下，它將按文件擴展名自動檢測壓縮。

所以我們可以看出來上面的insert語句中，第一個參數是s3的url,第二個參數是說明文件為TSV文件，第三個參數把表的結構寫下來了，這里沒有給第四個參數，因為默認檢測了擴展名為xz，所以不需要提供

2.從本地導入

這里可以通過官網獲取更詳細的內容

2.1 從本地其他文件導入

# 寫法一：
clickhouse-client -q "INSERT INTO git.com FORMAT CSV" < out.csv
# 寫法二：
INSERT INTO com
FROM INFILE '/data/tools/out.csv'
FORMAT CSV
# 寫法三：
cat out.csv|clickhouse-client -q "INSERT INTO git.com FORMAT CSV"

2.2 從本地sql文件導入

ddl語句將被跳過

INSERT INTO com
FROM INFILE '/data/tools/dump.sql' FORMAT MySQLDump

ps: 也可以直接通過文件讀取數據,但是要注意讀取文件的位置要在這個配置里面：user_files_path，不然會沒有權限

 SELECT *
FROM file('dump.sql', MySQLDump)
LIMIT 5

2.3 二進制文件

詳細內容可以在官網中查看

INSERT INTO com
FROM INFILE '/data/tools/data.clickhouse' FORMAT NativeINSERT INTO com
FROM INFILE '/data/tools/data.clickhouse' COMPRESSION 'lz4' FORMAT NativeINSERT INTO com
FROM INFILE '/data/tools/data.binary' FORMAT RowBinaryINSERT INTO com
FROM INFILE '/data/tools/data.msgpk' FORMAT MsgPack

ps: 導入之前可以用以下語句查看下文件內容，不過也有同樣的限制，文件的位置要在user_files_path配置里

DESCRIBE file('data.clickhouse', Native);

三、導出

1.導出數據到s3

1.1 從s3中查詢數據

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_*.gz', 'TabSeparatedWithNames')
LIMIT 10;SELECT  _path, _file, trip_id
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_0.gz', 'TabSeparatedWithNames')
LIMIT 5;

注意： 我們不需要列出列，因為 TabSeparatedWithNames 格式對第一行中的列名稱進行編碼。 其他格式（例如 CSV 或 TSV）將為此查詢返回自動生成的列，例如 c1、c2、c3 等。
查詢還支持虛擬列 _path 和 _file，它們分別提供有關存儲桶路徑和文件名的信息。

1.2 導出數據到s3

# 導出數據到s3的單個文件中
INSERT INTO FUNCTIONs3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/csv/trips.csv.lz4','s3_key','s3_secret','CSV')
SELECT *
FROM trips
LIMIT 10000;
# 導出數據到s3的多個文件中
INSERT INTO FUNCTIONs3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/csv/trips_{_partition_id}.csv.lz4','s3_key','s3_secret','CSV')PARTITION BY rand() % 10
SELECT *
FROM trips
LIMIT 100000;

ps: clickhouse還有更多和s3聯動的高級方式，這里先不做說明，后續講解

2.導出到本地

2.1其他文件

SELECT *
FROM com
INTO OUTFILE '/data/tools/out.csv'
FORMAT CSVWithNames

2.2 sql文件

涉及到的相關參數：

• output_format_sql_insert_table_name： 指定導出表的名稱，默認值為table
• output_format_sql_insert_include_column_names：指定在插入查詢中是否包含列，默認包含，設置為0則不包含
• output_format_sql_insert_max_batch_size：一個insert語句中的最大行數，默認為65505
• output_format_sql_insert_quote_names ：是否用反引號包含字段
• output_format_sql_insert_use_replace：使用replace語句而不是用insert語句，默認值為false

select * from commits limit 100 into outfile '/data/tools/dump.sql' truncate format SQLInsert;
SET output_format_sql_insert_table_name = 'out_table';
select * from table1 into outfile '/data/tools/dump.sql' [append|truncate] format SQLInsert;
# 可以看到/data/tools/dump.sql的文件內容如下，sql的名字變成了out_table
INSERT INTO out_table (`id`, `column1`) VALUES (1, 'abc');

2.3 二進制文件

SELECT * FROM com
INTO OUTFILE '/data/tools/data.clickhouse' FORMAT NativeSELECT * FROM com
INTO OUTFILE '/data/tools/data.clickhouse' COMPRESSION 'lz4' FORMAT NativeSELECT * FROM com
INTO OUTFILE '/data/tools/data.binary' FORMAT RowBinary# 這里必須要用limit1,多導出的話文件格式就不對了，jpg圖片無法正常打開
SELECT * FROM com limit 1
INTO OUTFILE '/data/tools/data.jpg'FORMAT RawBLOBSELECT * FROM com
INTO OUTFILE '/data/tools/data.msgpk'FORMAT MsgPack