笔记~~~~~
导出表结构
SELECT(casewhen a.colorder = 1 then d.nameelse nullend) 表名,a.colorder 字段序号,a.name 字段名,(casewhen COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 then '√'else ''end) 标识,(casewhen (SELECTcount(*)FROMsysobjectsWHERE(name in (SELECTnameFROMsysindexesWHERE(id = a.id)AND (indid in (SELECTindidFROMsysindexkeysWHERE(id = a.id)AND (colid in (SELECTcolidFROMsyscolumnsWHERE(id = a.id)AND (name = a.name)))))))AND (xtype = 'PK')) > 0 then '√'else ''end) 主键,b.name 类型,a.length 占用字节数,COLUMNPROPERTY(a.id, a.name, 'PRECISION') as 长度,isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) as 小数位数,(casewhen a.isnullable = 1 then '√'else ''end) 允许空,convert(varchar(200), isnull(e.text, '')) 默认值,convert(varchar(200), isnull(g.[value], ' ')) AS 说明
FROMsyscolumns aleft join systypes b on a.xtype = b.xusertypeinner join sysobjects d on a.id = d.idand d.xtype = 'U'and d.name <> 'dtproperties'left join syscomments e on a.cdefault = e.idleft join sys.extended_properties g on a.id = g.major_idAND a.colid = g.minor_idleft join sys.extended_properties f on d.id = f.classand f.minor_id = 0
whered.name = 'table_name'
order bya.id,a.colorder
查看cdc
select index_name from cdc.change_tables where capture_instance='table_name'
select * from cdc.table_name_CT
select sys.fn_cdc_map_lsn_to_time([ __ $ start_lsn ]) as versionnumber,* from cdc.table_name_CT
创建kudu表
CREATE TABLE dl_fw_kudu.kudu_table_name (id STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,column_name STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,...PRIMARY KEY (id)
) PARTITION BY HASH (id) PARTITIONS 20 STORED AS KUDU
创建hive seq格式表
CREATE external TABLE tt_table_name_seq (column_name string COMMENT '字段名称',dl_etl_date string COMMENT '落数时间'
) COMMENT '中文表名' PARTITIONED BY (` pt ` string COMMENT '日期') row format delimited fields terminated by '\001' stored as sequencefile tblproperties("creator" = "创建者");
创建hive txt格式表
CREATE TABLE IF NOT EXISTS tt_table_name (column_name string NOT NULL COMMENT '字段名',dl_etl_date string comment '落数时间'
) COMMENT '中文表名' ROW FORMAT delimited fields terminated BY '\001' stored as parquet tblproperties("creator" = "创建者");
创建doris表
CREATE TABLE table_name (column_name string NULL COMMENT "字段名",dl_etl_date string NULL COMMENT "落数时间"
) ENGINE=OLAP
UNIQUE KEY(`column_name_1`)
COMMENT "中文表名"
DISTRIBUTED BY HASH(`column_name_1`) BUCKETS 3 -- 一般如果分区多，桶就小一些，一个桶100-200万数据
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "DEFAULT",
"enable_persistent_index" = "false",
"compression" = "LZ4"
);##
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false", -- 是否放入内存（放入内存会快些，但可能会占用存储空间）
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH", -- 分区单位DAY,MONTH
"dynamic_partition.start" = "-24", --保存历史分区范围
"dynamic_partition.end" = "12", --提前创建历史分区范围
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"dynamic_partition.replication_num" = "3"
);
primary key：主键模型，新进的数据会根据key更新，不允许出现相同的key，适合高频更新且数据量较大的模型，占用内存，一般不轻易使用
DUPLICATE KEY：key值可重复，不支持唯一主键的场景
unique key：效果和primary key一模一样，区别是primary key性能更好，unique key不占用内存
笔记~~~~~
导出表结构
SELECT(casewhen a.colorder = 1 then d.nameelse nullend) 表名,a.colorder 字段序号,a.name 字段名,(casewhen COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 then '√'else ''end) 标识,(casewhen (SELECTcount(*)FROMsysobjectsWHERE(name in (SELECTnameFROMsysindexesWHERE(id = a.id)AND (indid in (SELECTindidFROMsysindexkeysWHERE(id = a.id)AND (colid in (SELECTcolidFROMsyscolumnsWHERE(id = a.id)AND (name = a.name)))))))AND (xtype = 'PK')) > 0 then '√'else ''end) 主键,b.name 类型,a.length 占用字节数,COLUMNPROPERTY(a.id, a.name, 'PRECISION') as 长度,isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) as 小数位数,(casewhen a.isnullable = 1 then '√'else ''end) 允许空,convert(varchar(200), isnull(e.text, '')) 默认值,convert(varchar(200), isnull(g.[value], ' ')) AS 说明
FROMsyscolumns aleft join systypes b on a.xtype = b.xusertypeinner join sysobjects d on a.id = d.idand d.xtype = 'U'and d.name <> 'dtproperties'left join syscomments e on a.cdefault = e.idleft join sys.extended_properties g on a.id = g.major_idAND a.colid = g.minor_idleft join sys.extended_properties f on d.id = f.classand f.minor_id = 0
whered.name = 'table_name'
order bya.id,a.colorder
查看cdc
select index_name from cdc.change_tables where capture_instance='table_name'
select * from cdc.table_name_CT
select sys.fn_cdc_map_lsn_to_time([ __ $ start_lsn ]) as versionnumber,* from cdc.table_name_CT
创建kudu表
CREATE TABLE dl_fw_kudu.kudu_table_name (id STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,column_name STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,...PRIMARY KEY (id)
) PARTITION BY HASH (id) PARTITIONS 20 STORED AS KUDU
创建hive seq格式表
CREATE external TABLE tt_table_name_seq (column_name string COMMENT '字段名称',dl_etl_date string COMMENT '落数时间'
) COMMENT '中文表名' PARTITIONED BY (` pt ` string COMMENT '日期') row format delimited fields terminated by '\001' stored as sequencefile tblproperties("creator" = "创建者");
创建hive txt格式表
CREATE TABLE IF NOT EXISTS tt_table_name (column_name string NOT NULL COMMENT '字段名',dl_etl_date string comment '落数时间'
) COMMENT '中文表名' ROW FORMAT delimited fields terminated BY '\001' stored as parquet tblproperties("creator" = "创建者");
创建doris表
CREATE TABLE table_name (column_name string NULL COMMENT "字段名",dl_etl_date string NULL COMMENT "落数时间"
) ENGINE=OLAP
UNIQUE KEY(`column_name_1`)
COMMENT "中文表名"
DISTRIBUTED BY HASH(`column_name_1`) BUCKETS 3 -- 一般如果分区多，桶就小一些，一个桶100-200万数据
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "DEFAULT",
"enable_persistent_index" = "false",
"compression" = "LZ4"
);##
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false", -- 是否放入内存（放入内存会快些，但可能会占用存储空间）
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH", -- 分区单位DAY,MONTH
"dynamic_partition.start" = "-24", --保存历史分区范围
"dynamic_partition.end" = "12", --提前创建历史分区范围
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"dynamic_partition.replication_num" = "3"
);
primary key：主键模型，新进的数据会根据key更新，不允许出现相同的key，适合高频更新且数据量较大的模型，占用内存，一般不轻易使用
DUPLICATE KEY：key值可重复，不支持唯一主键的场景
unique key：效果和primary key一模一样，区别是primary key性能更好，unique key不占用内存