笔记~~~~~

导出表结构

SELECT
    (
        case
            when a.colorder = 1 then d.name
            else null
        end
    ) 表名,
    a.colorder 字段序号,
    a.name 字段名,
    (
        case
            when COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 then '√'
            else ''
        end
    ) 标识,
    (
        case
            when (
                SELECT
                    count(*)
                FROM
                    sysobjects
                WHERE
                    (
                        name in (
                            SELECT
                                name
                            FROM
                                sysindexes
                            WHERE
                                (id = a.id)
                                AND (
                                    indid in (
                                        SELECT
                                            indid
                                        FROM
                                            sysindexkeys
                                        WHERE
                                            (id = a.id)
                                            AND (
                                                colid in (
                                                    SELECT
                                                        colid
                                                    FROM
                                                        syscolumns
                                                    WHERE
                                                        (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 小数位数,
    (
        case
            when a.isnullable = 1 then '√'
            else ''
        end
    ) 允许空,
    convert(varchar(200), isnull(e.text, '')) 默认值,
    convert(varchar(200), isnull(g.[value], ' ')) AS 说明
FROM
    syscolumns a
    left join systypes b on a.xtype = b.xusertype
    inner join sysobjects d on a.id = d.id
    and d.xtype = 'U'
    and d.name <> 'dtproperties'
    left join syscomments e on a.cdefault = e.id
    left join sys.extended_properties g on a.id = g.major_id
    AND a.colid = g.minor_id
    left join sys.extended_properties f on d.id = f.class
    and f.minor_id = 0
where
    d.name = 'table_name'
order by
    a.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不占用内存