flink写数据到iceberg

一、创建iceberg环境

1、创建iceberg的存储环境

CREATE CATALOG  iceberg_catalog WITH (
  'type'='iceberg',
  'catalog-type'='hadoop',
  'warehouse'='hdfs://hadoop01:4007/data-lake/iceberg/warehouse/catalog',
  'property-version'='2'
);

-- /datalake/iceberg/warehouse

2、在iceberg下创建数据库

create database bigdata_ods; -- 默认情况下创建好了catalog后会有一个默认的default的数据库
create database crisps_bigdata_ads;

3、库下创建表

CREATE TABLE crisps_bigdata_ads.user_info
(
    `mch_uid`           bigint        NOT NULL COMMENT '商户用户ID',
    `mch_uname`         varchar(2560) NULL COMMENT '商户用户姓名',
    `uid`               bigint        NULL COMMENT '用户中心ID',
    `uno`               varchar(2560) NULL COMMENT '平台工号',
    `outno`             varchar(2560) NULL COMMENT '外部工号',
    `mch_post_id`       bigint        NULL COMMENT '职位id',
    `office_address_id` bigint        NULL COMMENT '办公地址id',
    `city_code`         varchar(2560) NULL COMMENT '市code',
    `city_name`         varchar(2560) NULL COMMENT '市名称',
    `brand_id`          bigint        NULL COMMENT '品牌id',
    `brand_name`        varchar(2560) NULL COMMENT '品牌名称',
    `load_time`         timestamp(0)     NULL COMMENT '数据加载时间'
);

4、flink模式设置为批

SET 'sql-client.execution.result-mode' = 'tableau';
RESET 'execution.checkpointing.interval';
SET 'execution.runtime-mode' = 'batch';

5、往新建的表插入数据

INSERT INTO crisps_bigdata_ads.user_info VALUES (1, '小李', 11212112212121, 'U0010', '3050069', 1213112312, 1212121122121, '510100', '成都市', 121212121212, 'hadoop有限公司', now());

6、创建有分区信息的表

CREATE TABLE crisps_bigdata_ads.user_info_p
(
    `mch_uid`           bigint        NOT NULL COMMENT '用户ID',
    `mch_uname`         varchar(2560) NULL COMMENT '姓名',
    `uid`               bigint        NULL COMMENT '用户中心ID',
    `uno`               varchar(2560) NULL COMMENT '工号',
    `outno`             varchar(2560) NULL COMMENT '外部工号',
    `mch_post_id`       bigint        NULL COMMENT '职业id',
    `office_address_id` bigint        NULL COMMENT '地址id',
    `city_code`         varchar(2560) NULL COMMENT '市',
    `city_name`         varchar(2560) NULL COMMENT '市名称',
    `brand_id`          bigint        NULL COMMENT '品牌id',
    `brand_name`        varchar(2560) NULL COMMENT '品牌名称',
    `load_time`         timestamp(0)  NULL COMMENT '数据加载时间',
    `dt`                date  NULL COMMENT '加载日期'
) PARTITIONED BY (dt);

7、向分区表写数据

INSERT INTO crisps_bigdata_ads.user_info_p VALUES (1, '小李', 11212112212121, 'U0010', '3050069', 1213112312, 1212121122121, '510100', '成都市', 121212121212, 'hadoop有限公司', now(),to_date(cast(now() as string)) );

8、CREATE TABLE LIKE创建表

CREATE TABLE  crisps_bigdata_ads.user_info_p_like  LIKE crisps_bigdata_ads.user_info_p;

9、创建upsert表

需要加上配置**‘write.upsert.enabled’=‘true’**

CREATE TABLE crisps_bigdata_ads.u_k
(
    `mch_uid`           bigint        NOT NULL COMMENT '用户ID',
    `mch_uname`         varchar(2560) NULL COMMENT '姓名',
    `uid`               bigint        NULL COMMENT '用户中心ID',
    `uno`               varchar(2560) NULL COMMENT '工号',
    `outno`             varchar(2560) NULL COMMENT '外部工号',
    `mch_post_id`       bigint        NULL COMMENT '职业id',
    `office_address_id` bigint        NULL COMMENT '地址id',
    `city_code`         varchar(2560) NULL COMMENT '市',
    `city_name`         varchar(2560) NULL COMMENT '市名称',
    `brand_id`          bigint        NULL COMMENT '品牌id',
    `brand_name`        varchar(2560) NULL COMMENT '品牌名称',
    `load_time`         timestamp(0)  NULL COMMENT '数据加载时间',
    `dt`                date  NULL COMMENT '加载日期'
    primary key(mch_uid) NOT ENFORCED
) 
with (
    'format-version'='2', 
    'write.upsert.enabled'='true',
    'write.format.default' = 'PARQUET'
);
;

10、向主键表插入数据

INSERT INTO crisps_bigdata_ads.u_k VALUES (1, '小李', 11212112212121, 'U0010', '3050069', 1213112312, 1212121122121, '510100', '成都市', 121212121212, 'hadoop有限公司', now(),to_date(cast(now() as string)) );