CREATE TABLE

最近更新时间:2024-02-26 10:21:41

我的收藏

说明

支持内核:Presto、SparkSQL。
适用表范围:原生 Iceberg 表、外部表。
用途:创建一个表同时带一些属性,支持使用 CREATE TABLE AS 语法。
建表存储路径:建表存储路径支持指定至 COS 目录,不能指定到文件。

外部表语法

语法

CREATE TABLE [ IF NOT EXISTS ] table_identifier
( col_name[:] col_type [ COMMENT col_comment ], ... )
USING data_source
[ COMMENT table_comment ]
[ OPTIONS ( 'key1'='value1', 'key2'='value2' )]
[ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
[ LOCATION path ]
[ TBLPROPERTIES ( property_name=property_value, ... ) ]

参数

USING data_source:建表时,数据的输入类型,目前有:CSV,ORC,PARQUET,ICEBERG 等。 table_identifier:指定表名,支持三段式,例如:catalog.database.table。 COMMENT:表的描述信息。
OPTIONS:USING data_source支持的额外参数,用于存储时参数注入。 PARTITIONED BY:基于指定的列创建分区。 LOCATION path:数据表存储路径。 TBLPROPERTIES:一组 k-v 值,用于指定表的参数。

USING和OPTIONS 参数详细说明

USING CSV
USING ORC
USING PARQUET
参考链接:Working with CSV
CSV 数据表的可支持配置如下。
OPTIONS 支持的 key
key 对应的 value 默认值
含义
sep或delimiter
,
csv存储时每列之间的分隔符,默认英文逗号
mode
PERMISSIVE
定义当数据转换时不符合预期时的处理模式。
PERMISSIVE:较宽松的模式,默认,尝试转换某一行数据,例如某一行多出来几列,会自动只取需要的列
DROPMALFORMED:丢弃不符合预期的数据,例如某一个行多出来列则该行会被丢弃
FAILFAST:严格要求csv格式,一旦某行不符合预期就失败,例如多出列的情况。
encoding或charset
UTF-8
字符串编码格式。
例如:UTF-8、US-ASCII、ISO-8859-1、UTF-16BE、UTF-16LE、UTF-16
quote
\\"
引号是单引号还是双引号,注意使用转义符
escape
\\\\
逃逸字符,注意使用转义符
charToEscapeQuoteEscaping
-
引号内部需要逃逸的字符
comment
\\u0000
备注信息
header
false
存在表头
inferSchema
false
推断每列类型,不推断则每一列均为字符串
ignoreLeadingWhiteSpace
读:false
写:true
忽略开头的空字符串
ignoreTrailingWhiteSpace
读:false
写:true
忽略结尾的空字符串
columnNameOfCorruptRecord
_corrupt_record
无法转换的列的列名,该参数受spark.sql.columnNameOfCorruptRecord影响,以表配置为主
nullValue
-
null的存储格式,默认为空字符串,此时按emptyValue的方式写
nanValue
NaN
非数值类型的值的存储格式
positiveInf
Inf
正无穷大的存储格式
negativeInf
-Inf
负无穷大的存储格式
compression或codec
-
压缩算法的类名,默认不压缩,可以使用简称,bzip2、deflate、gzip、lz4、snappy
timeZone
系统默认时区
默认时区,该参数取值受spark.sql.session.timeZone影响,例如Asia/Shanghai,以表配置为主
locale
en-US
语言类型
dateFormat
yyyy-MM-dd
默认日期的格式
timestampFormat
yyyy-MM-dd'T'HH:mm:ss.SSSXXX
默认时间的格式,非LEGACY模式下为yyyy-MM-dd'T'HH:mm:ss[.SSS][XXX]
multiLine
false
允许多行
maxColumns
20480
最大列数
maxCharsPerColumn
-1
每列最大字符数,-1表示不限制
escapeQuotes
true
逃逸引号
quoteAll
quoteAll
写时全文加引号
samplingRatio
1.0
采样比例
enforceSchema
true
强制使用指定的schema读取,会忽略表头的定义
emptyValue
读:
写:\\"\\"
空值的读写格式
lineSep
-
换行符
inputBufferSize
-
读时的buffer size,该参数可受spark.sql.csv.parser.inputBufferSize影响,以表配置为主
unescapedQuoteHandling
STOP_AT_DELIMITER
非逃逸引号被发现时的处理策略。
STOP_AT_DELIMITER:读到分隔符停止
BACK_TO_DELIMITER:回退到分隔符
STOP_AT_CLOSING_QUOTE:读到下一个引号停止
SKIP_VALUE:跳过该列数据
RAISE_ERROR:报错

ORC 数据表可支持的配置如下:
OPTIONS 支持的 key
key 对应的 value 默认值
含义
compression或orc.compress
snappy
压缩算法,支持简写snappy/zlib/lzo/lz3/zstd,该参数受spark.sql.orc.compression.codec影响,以表参数为主
mergeSchema
false
合并schema,该参数受spark.sql.orc.mergeSchema影响,以表参数为主
如果是使用 HiveRead 和 HiveWriter(配置spark.sql.hive.convertMetastoreOrc=false)来读写,OPTIONS 还可以支持 Orc 原生的配置,详情请参考 LanguageManual ORC
PARQUET 数据表相关参数大多可以通过 Spark conf 配置,也更建议从 spark conf 配置。options 也可支持的配置如下:
OPTIONS 支持的 key
key 对应的 value 默认值
含义
compression或parquet.compression
snappy
压缩算法,默认使用snappy,受参数spark.sql.parquet.compression.codec影响,以表参数为主。
mergeSchema
false
是否合并schema,受参数spark.sql.parquet.mergeSchema影响,以表参数为主。
datetimeRebaseMode
EXCEPTION
写parquet文件时日期的转换策略。LEGACY模式将日期做公历转换、CORRECTED不对日期做公历转换、EXCEPTION在遇到日期属于不同格式时报错。受参数spark.sql.parquet.datetimeRebaseModeInRead影响,以表参数为主。
int96RebaseMode
EXCEPTION
读parquet文件时时间的转换策略。LEGACY模式将对时间做公历转换、CORRECTED不对时间做转换、EXCEPTION则在遇到不同格式的时间时报错。受参数spark.sql.parquet.int96RebaseModeInRead影响,以表参数为主。
如果是使用HiveRead和HiveWriter(配置spark.sql.hive.convertMetastoreParquet=false)来读写,OPTIONS还可以支持Parquet原生的配置参考:Hadoop integration

示例

CREATE TABLE dempts(
id bigint COMMENT 'id number',
num int,
eno float,
dno double,
cno decimal(9,3),
flag boolean,
data string,
ts_year timestamp,
date_month date,
bno binary,
point struct<x: double, y: double>,
points array<struct<x: double, y: double>>,
pointmaps map<struct<x: int>, struct<a: int>>
)
USING iceberg
COMMENT 'table documentation'
PARTITIONED BY (bucket(16,id), years(ts_year), months(date_month), identity(bno), bucket(3,num), truncate(10,data))
LOCATION '/warehouse/db_001/dempts'
TBLPROPERTIES ('write.format.default'='orc');

常见问题

CREATE_TABLE 时的关键字里,Spark 的 USING 和 Hive 的 STORED AS 存在差异,可能会导致创建表后文件格式、读取都不符合预期。这里做一个特殊说明:
USING DATA_SOURCE:Spark 语法,该关键字表示创建表时使用哪种数据源作为输入的格式,直接影响表的 Location 下的文件格式和读取方式。可取值例如 CSV、TXT、Iceberg、Parquet、Orc 等。
STORED AS FILE_FORMAT:Hive 语法,该关键字用于创建一个 HIVE 格式的表,表示表中存储的数据文件的格式。可取值例如 TXT、Parquet、Orc 等。不建议使用这种语法,可能会导致 Spark 原生的 reader/writer 无法支持,例如不支持 CSV。

原生表 Iceberg 语法

注意
该语法仅支持创建原生表。

语法

CREATE TABLE [ IF NOT EXISTS ] table_identifier
( col_name[:] col_type [ COMMENT col_comment ], ... )
[ COMMENT table_comment ]
[ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]

参数

table_identifier:支持三段式,catalog.db.name Schemas and Data Types
col_type
: primitive_type
  | nested_type

primitive_type
: boolean
| int/integer
| long/bigint
| float
| double
| decimal(p,s),p=最大位数,s=最大小数点位数, s<=p<=38
| date
| timestamptimestamp with timezone,不支持time和without timezone
| string,也可对应Iceberg uuid类型
| binary,也可对应Iceberg fixed类型

nested_type
: struct
| list
| map
Partition Transforms
transform
: identity,支持任意类型, DLC不支持该转换
| bucket[N]hash mod N分桶,支持col_type: int,long, decimal, date, timestamp, string, binary
| truncate[L],L截取分桶,支持col_type: int,long,decimal,string
| years,年份,支持col_type: date,timestamp
| months,月份,支持col_type: date,timestamp
| days/date,日期,支持col_type: date,timestamp
| hours/date_hour,小时,支持col_type: timestamp

示例

CREATE TABLE dempts(
id bigint COMMENT 'id number',
num int,
eno float,
dno double,
cno decimal(9,3),
flag boolean,
data string,
ts_year timestamp,
date_month date,
bno binary,
point struct<x: double, y: double>,
points array<struct<x: double, y: double>>,
pointmaps map<struct<x: int>, struct<a: int>>
)
COMMENT 'table documentation'
PARTITIONED BY (bucket(16,id), years(ts_year), months(date_month), identity(bno), bucket(3,num), truncate(10,data));