spark 写入pgsql timestamp spark 写入clickhouse array类型_csv输入的第一行字段数有错


ClickHouse最佳实战之Clickhouse的输入输出数据格式详解

官网：


Distinctive Features | ClickHouse Documentationclickhouse.tech

spark 写入pgsql timestamp spark 写入clickhouse array类型_csv输入的第一行字段数有错_02


CLickHouse拥有丰富的输入输出格式，对不同的输入输出格式特性的理解有利于对数据的导入，查询的展示，CLickHouse主要分为7种类型系列的输入输出格式，分别是

1、tabseparated系列格式

2、tskv格式

3、csv系列格式

4、json系列格式

5、parquet格式

6、orc格式

7、其他常用的数据格式

一、TabSeparated、TabSeparatedRaw、TabSeparatedWithNames和TabSeparatedWithNamesAndTypes

1. TabSeparated

数据按行写入，tab制表符分隔。使用严格Unix命令行。

注意：最后一行必须包含换行符。

默认格式。

简写：TSV

数据插入和数据查询时，均可使用。

创建表

CREATE TABLE tsv_demo(srcip String, destip String, time String) ENGINE = TinyLog;

数据导入

clickhouse-client --query "INSERT INTO tsv_demo FORMAT TabSeparated" --max_insert_block_size=100000 < tsv_demo.tsv 
Code: 117. DB::Exception: 
You have carriage return (r, 0x0D, ASCII 13) at end of first row.
It's like your input data has DOS/Windows style line separators, that are illegal in TabSeparated format. You must transform your file to Unix format.
But if you really need carriage return at end of string value of last column, you need to escape it as r.

这是由于windows使用rn表示回车换行， 在linux中使用n表示换行。 因此，需要将r给去掉。

转换为UNIX格式：

dos2unix tsv_demo.tsv

输出格式：

select * from tsv_demo FORMAT TSV;

2. TabSeparatedRaw

简称：TSVRaw

TabSeparatedRaw格式不会对行数据进行转义， 即不会将换行、制表符等转换为转义字符。

只能在数据查询的时候使用。

zhangsan nanjingtjiangsu 23 From nanjing

lisi hangzhoutzhejiang 32 x41 amazing place

xiaoming hefeitanhui 25 notepad

建表语句：

create table escape_demo(name String, addr String, age UInt8, desc String) ENGINE=TinyLog;
select * from escape_demo FORMAT TSV;
zhangsan nanjingtjiangsu 23 From nanjing
lisi hangzhoutzhejiang 32 A amazing place
xiaoming hefeitanhui 25 notepad
3 rows in set. Elapsed: 0.005 sec.
SELECT *
FROM escape_demo
FORMAT TabSeparatedRaw
zhangsan nanjing jiangsu 23 From nanjing
lisi hangzhou zhejiang 32 A amazing place
xiaoming hefei anhui 25 notepad
3 rows in set. Elapsed: 0.005 sec
3. TabSeparatedWithNames

在查询时，TabSeparatedWithNames格式的第一行显示列的名称

在数据导入时，第一行完全被忽略，不会解析第一行为表头。

简称：TSVWithNames

在数据查询和数据导入均可使用。

数据导入：

clickhouse-client --query="INSERT INTO escape_demo FORMAT TSVWithNames" < escape_demo.tsv

数据查询：

select * from escape_demo FORMAT TSVWithNames;

4. TabSeparatedWithNamesAndTypes

在查询时，TabSeparatedWithNamesAndTypes格式在主数据的前面额外显示两行数据，第一行显示列的名称， 第二行显示列的数据类型。

在数据导入时，前面两行的数据完全被忽略。

简写：TSVWithNamesAndTypes

在数据导入和查询时均可使用。

二、TSKV

TSKV格式不适合有大量小列的输出.

TSKV的效率并不比JSONEachRow差.

TSKV数据查询和数据导入。

不需要保证列的顺序。

支持忽略某些值，这些列使用默认值，例如0和空白行。复杂类型的值必须指定，无法使用默认值。

查看：

select * from escape_demo FORMAT TSKV;

导出：

clickhouse-client --query "select * from escape_demo FORMAT TSKV" > tskv.demo

导入：

clickhouse-client --query "insert into escape_demo FORMAT TSKV" < tskv.demo

三、CSV格式：CSV、CSVWithNames

1. CSV格式：CSV、CSVWithNames

CSV默认的分隔符为逗号，format_csv_delimiter设置自定义的分隔符。

CSV中的双引号使用两个双引号转义。

支持数据的查询和数据导入的。

create table csv_demo(create_date Date, update_time DateTime, desc String) ENGINE=TinyLog;

2014-03-23|2014-03-23 14:10:14|Apache Spark achieves high performance

2014-03-23|2014-03-23 15:10:30|Spark offers over 80 high-level operators

1395990600|1395904200|Learning Apache "Spark" is easy

数据导入：

clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO csv_demo FORMAT CSV" < csv_dmeo.csv

数据查看：

set format_csv_delimiter='|'
select * from csv_demo FORMAT CSV;

2. CSVWithNames

CSVWithNames会打印表头的信息。

支持数据的导入和数据的查看。

select * from csv_demo FORMAT CSVWithNames;

四、JSON系列格式：JSON、JSONCompact和JSONEachRow

1. JSON

JSON格式只支持数据的输出，不支持数据的解析（数据导入）。

create table t_json_demo(id UInt8, prov String) ENGINE=TinyLog;
insert into t_json_demo values(1, 'jiangsu'),
(1, 'jiangsu'),
(2, 'anhui'),
(2, 'anihu'),
(3, 'beijing');

默认情况下， Int64和UInit64的整型使用双引号包裹， 如果要移除双引号， 设置配置参数output_format_json_quote_64bit_integers为0。

rows_before_limit_at_least：只有查询包含LIMIT时才输出， 只有在包含group by的语句中才有意义。当查询没有LIMIT时， 执行结果的最小行数。

2. JSONCompact

JSON格式的数据以对象的方式输出， 而JSONCompact以数组的方式输出。

JSONCompact只支持数据的查看， 不支持数据的导入。

3. JSONEachRow

每行数据以换行符分隔的JSON对象。

支持数据的输出和数据导入。

数据导入：

对象中键值对的顺序可任意排列。

缺失某些字段。

create table UserActivity (PageViews UInt8, UserID String, Duration UInt64, Sign Int8) ENGINE TinyLog;

INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

缺失值的处理：

CREATE TABLE IF NOT EXISTS example_table
(
 x UInt32,
 a DEFAULT x + 2
) ENGINE = Memory;
insert into example_table FORMAT JSONEachRow {"x":3, "a":5} {"x":4};

参数：input_format_defaults_for_omitted_fields

如果为0， 则x和a的默认值等于0（即UInt32数据类型的默认值）。

如果为1， 则x的默认值等于0， 但a的默认值等于x+2。

input_format_defaults_for_omitted_fields=1

嵌套结构的数据

CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory;
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]};
{"n": { "s": ["abc", "def"],"i": [1, 23]}}

要将数据作为分层JSON对象插入， 需要设置input_format_import_nested_json=1。

当需要设置input_format_import_nested_json=0时：

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}

Exception on client:

Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n
Connecting to 192.168.0.200:9000 as user default.
Connected to ClickHouse server version 20.1.4 revision 54431.

五、Parquet数据格式

支持Parquet格式的导出和导入。

Parquet和ClickHouse类型的匹配关系：

Parquet data type (INSERT)| ClickHouse data type| Parquet data type (SELECT)
UINT8,BOOL UInt8 UINT8
INT8 Int8 INT8
UINT16 UInt16 UINT16
INT16 Int16 INT16
UINT32 UInt32 UINT32
INT32 Int32 INT32
UINT64 UInt64 UINT64
INT64 Int64 INT64
FLOAT,HALF_FLOAT Float32 FLOAT
DOUBLE Float64 DOUBLE
DATE32 Date UINT16
DATE64,TIMESTAMP DateTime UINT32
STRING,BINARY String STRING
— FixedString STRING
DECIMAL Decimal DECIMAL

不支持的Parquet数据类型：DATE32, TIME32, FIXED_SIZE_BINARY, JSON, UUID, ENUM。

注意： ClickHouse表的列名必须与Parquet表的列名一致。

ClickHouse表的列数据类型可以不同于插入的Parquet数据类型。 在插入数据时， ClickHouse根据上表解释数据类型， 然后将数据类型转换为ClickHouse表的列数据类型。

数据的导出：

clickhouse-client --query="SELECT * FROM tsv_demo FORMAT Parquet" > parquet_demo.parquet

数据的导入：

create table parquet_demo (srcip String, destip String, time String) ENGINE=TinyLog;
cat parquet_demo.parquet | clickhouse-client --query="INSERT INTO parquet_demo FORMAT Parquet"

六、ORC

仅支持ORC格式的写入。

ORC和CH数据类型的匹配关系：

ORC data type (INSERT)|ClickHouse data type
UINT8, BOOL UInt8
INT8 Int8
UINT16 UInt16
INT16 Int16
UINT32 UInt32
INT32 Int32
UINT64 UInt64
INT64 Int64
FLOAT, HALF_FLOAT Float32
DOUBLE Float64
DATE32 Date
DATE64, TIMESTAMP DateTime
STRING, BINARY String
DECIMAL Decimal

不支持的ORC数据类型：DATE32, TIME32, FIXED_SIZE_BINARY, JSON, UUID, ENUM。

Note： ClickHouse表的列名必须与ORC表的列名一致。

使用Spark生成ORC文件：

val list = List(
 ("113.248.234.232", "123.212.22.01", "2018-07-12 14:35:31"),
 ("115.248.158.231", "154.245.56.23", "2020-07-12 13:26:26"),
 ("115.248.158.231", "154.245.56.23", "2020-07-12 13:22:13"),
 ("187.248.135.230", "221.228.112.45", "2019-08-09 13:17:39"),
 ("187.248.234.232", "221.228.112.24", "2019-08-09 20:51:16"),
 ("115.248.158.231", "154.245.56.23", "2020-07-12 17:22:56")
)
val rdd = sc.makeRDD(list)
import spark.implicits._
val df = rdd.toDF("srcip", "destip", "time")
df.repartition(1).write.format("orc").mode("append").save("/tmp/orc")

测试表：

create table orc_demo (srcip String, destip String, time DateTime) ENGINE=TinyLog;

数据导入：

cat file.orc | clickhouse-client --query="INSERT INTO orc_demo FORMAT ORC"

七、其他常用的数据格式：

1. Native

数据以二进制数据块的方式进行读写。

数据的导出：

clickhouse-client --query="SELECT * FROM tsv_demo FORMAT Native" > a.native

数据的导入：

clickhouse-client --query="insert into tsv_demo FORMAT Native" < a.native

2. Null

主要用于测试性能。 查询会被处理，并且数据会被传送到客户端，但是什么也不输出。

Null格式只能用于查询， 不能用于数据的导入。

3. Pretty

PrettyCompact：在交互式模式下，默认的数据显示格式。

PrettySpace

4. Values

每行之间使用逗号分隔，列之间也是使用逗号分隔。在括号中打印每一行。

INSERT INTO XX values
INSERT INTO XX FORMAT Values 
insert into tsv_demo FORMAT Values ('115.248.158.231', '115.248.158.232', '2020-07-12 17:22:56'), ('115.248.158.231', '115.248.158.232', '2020-07-12 17:22:56');

5. Vertical

数据以垂直的格式进行展示。 G

6. XML

只支持数据的查看。

select * from tsv_demo limit 2 FORMAT XML;