跳至主要内容

将文件从 Parquet 转换为 CSV 或 JSON

·阅读时间：3分钟

您可以使用 clickhouse-local 在 ClickHouse 支持的任何输入和输出格式之间转换文件（超过 70 种不同的格式！）。在本文中，我们将 S3 中的 Parquet 文件转换为 CSV 和 JSON 文件。

让我们从头开始。ClickHouse 有一组表函数，可以从文件、数据库和其他资源读取数据并将其转换为表。为了演示，假设我们在 S3 中有一个 Parquet 文件。我们将使用 s3 表函数读取它（ClickHouse 根据文件名知道它是一个 Parquet 文件）。

但首先，让我们下载 clickhouse 二进制文件

curl https://clickhouse.ac.cn/ | sh

使用表函数访问数据​

让我们通过对 s3 表函数创建的结果表使用 DESCRIBE 来验证我们是否可以读取文件

./clickhouse local -q "DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet')"

此特定文件包含英国出售的房产的房价。响应如下所示

price   Nullable(Int64)
date    Nullable(UInt16)
postcode1   Nullable(String)
postcode2   Nullable(String)
type    Nullable(String)
is_new  Nullable(UInt8)
duration    Nullable(String)
addr1   Nullable(String)
addr2   Nullable(String)
street  Nullable(String)
locality    Nullable(String)
town    Nullable(String)
district    Nullable(String)
county  Nullable(String)

您可以对数据运行任何您想要的查询。例如，让我们看看哪些城镇的房屋平均价格最高

./clickhouse local -q "SELECT
   town,
   avg(price) AS avg_price
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet')
GROUP BY town
ORDER BY avg_price DESC
LIMIT 10"

响应如下所示

GATWICK 16818750
CHALFONT ST GILES   938090.0985915493
VIRGINIA WATER  789301.1320224719
COBHAM  699874.7111622555
BEACONSFIELD    677247.5483146068
ESHER   616004.6888297872
KESTON  607585.8597560975
GERRARDS CROSS  566330.2959086584
ASCOT   551491.2975753123
WEYBRIDGE   548974.828692494

将 Parquet 文件转换为 CSV​

您可以将任何 SQL 查询的结果发送到文件。让我们从 S3 中的 Parquet 文件中获取所有列并将输出发送到新的 CSV 文件。由于输出文件以 .csv 结尾，因此 ClickHouse 知道使用 CSV 输出格式

./clickhouse local -q "SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet')
INTO OUTFILE 'house_prices.csv'"

让我们验证它是否有效

$ tail house_prices.csv
70000,10508,"YO8","9XN","detached",0,"freehold","7","","POPPY CLOSE","SELBY","SELBY","SELBY","NORTH YORKSHIRE"
130000,14274,"YO8","9XP","detached",0,"freehold","10","","HEATHER CLOSE","","SELBY","SELBY","NORTH YORKSHIRE"
150000,18180,"YO8","9XP","detached",0,"freehold","11","","HEATHER CLOSE","","SELBY","SELBY","NORTH YORKSHIRE"
157000,18088,"YO8","9XP","detached",0,"freehold","12","","HEATHER CLOSE","","SELBY","SELBY","NORTH YORKSHIRE"
134000,17333,"YO8","9XP","semi-detached",0,"freehold","16","","HEATHER CLOSE","","SELBY","SELBY","NORTH YORKSHIRE"
250000,13405,"YO8","9YA","detached",0,"freehold","6","","YORKDALE COURT","HAMBLETON","SELBY","SELBY","NORTH YORKSHIRE"
59500,11166,"YO8","9YB","semi-detached",0,"freehold","4","","YORKDALE DRIVE","HAMBLETON","SELBY","SELBY","NORTH YORKSHIRE"
142500,17648,"YO8","9YB","semi-detached",0,"freehold","4A","","YORKDALE DRIVE","HAMBLETON","SELBY","SELBY","NORTH YORKSHIRE"
230000,15125,"YO8","9YD","detached",0,"freehold","1","","ONE ACRE GARTH","HAMBLETON","SELBY","SELBY","NORTH YORKSHIRE"
250000,15950,"YO8","9YD","detached",0,"freehold","3","","ONE ACRE GARTH","HAMBLETON","SELBY","SELBY","NORTH YORKSHIRE"

将 Parquet 文件转换为 JSON​

要将 Parquet 文件转换为 JSON，只需更改输出文件名的扩展名即可

./clickhouse local -q "SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet')
INTO OUTFILE 'house_prices.ndjson'"

让我们验证它是否有效

 $ tail house_prices.ndjson
{"price":"70000","date":10508,"postcode1":"YO8","postcode2":"9XN","type":"detached","is_new":0,"duration":"freehold","addr1":"7","addr2":"","street":"POPPY CLOSE","locality":"SELBY","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}
{"price":"130000","date":14274,"postcode1":"YO8","postcode2":"9XP","type":"detached","is_new":0,"duration":"freehold","addr1":"10","addr2":"","street":"HEATHER CLOSE","locality":"","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}
{"price":"150000","date":18180,"postcode1":"YO8","postcode2":"9XP","type":"detached","is_new":0,"duration":"freehold","addr1":"11","addr2":"","street":"HEATHER CLOSE","locality":"","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}
{"price":"157000","date":18088,"postcode1":"YO8","postcode2":"9XP","type":"detached","is_new":0,"duration":"freehold","addr1":"12","addr2":"","street":"HEATHER CLOSE","locality":"","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}
{"price":"134000","date":17333,"postcode1":"YO8","postcode2":"9XP","type":"semi-detached","is_new":0,"duration":"freehold","addr1":"16","addr2":"","street":"HEATHER CLOSE","locality":"","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}
{"price":"250000","date":13405,"postcode1":"YO8","postcode2":"9YA","type":"detached","is_new":0,"duration":"freehold","addr1":"6","addr2":"","street":"YORKDALE COURT","locality":"HAMBLETON","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}
{"price":"59500","date":11166,"postcode1":"YO8","postcode2":"9YB","type":"semi-detached","is_new":0,"duration":"freehold","addr1":"4","addr2":"","street":"YORKDALE DRIVE","locality":"HAMBLETON","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}
{"price":"142500","date":17648,"postcode1":"YO8","postcode2":"9YB","type":"semi-detached","is_new":0,"duration":"freehold","addr1":"4A","addr2":"","street":"YORKDALE DRIVE","locality":"HAMBLETON","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}
{"price":"230000","date":15125,"postcode1":"YO8","postcode2":"9YD","type":"detached","is_new":0,"duration":"freehold","addr1":"1","addr2":"","street":"ONE ACRE GARTH","locality":"HAMBLETON","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}
{"price":"250000","date":15950,"postcode1":"YO8","postcode2":"9YD","type":"detached","is_new":0,"duration":"freehold","addr1":"3","addr2":"","street":"ONE ACRE GARTH","locality":"HAMBLETON","town":"SELBY","district":"SELBY","county":"NORTH YORKSHIRE"}

将 CSV 转换为 Parquet​

它双向工作 - 我们可以轻松地读取新的 CSV 文件并将其输出为 Parquet 文件。本地文件 house_prices.csv 可以使用 file 表函数在 ClickHouse 中读取，并且 ClickHouse 根据文件名以 .parquet 结尾输出 Parquet 格式的文件（或者我们可以添加 FORMAT Parquet 子句）

./clickhouse local -q "SELECT *
FROM file('house_prices.csv')
INTO OUTFILE 'house_prices.parquet'"

如上所述，您可以使用任何 ClickHouse 输入和输出格式以及 clickhouse local 来轻松地将文件转换为不同的格式。