iceberg upsert 主键问题建表语句参考

倾一生爱恋换一世纯真

已于 2024-07-09 16:03:06 修改

阅读量208

点赞数 1

文章标签： flink 数据库大数据

于 2024-07-08 15:28:28 首次发布

本文链接：https://blog.csdn.net/weixin_44236758/article/details/140270094

版权

hivesql 执行iceberg建表语句


CREATE TABLE `iczdcs1`(
  `id` INT NOT NULL,
  `int_test` INT,
  `bigint_test` BIGINT,
  `float_test` FLOAT,
  `double_test` DOUBLE,
  `decimal_test` DECIMAL(18, 2),
  `date_test` DATE,
  `varchar_test` STRING,
  `datetime_test` TIMESTAMP,
  primary key (`id`) NOT ENFORCED
)
STORED BY 'org.apache.iceberg.mr.hive.HiveIcebergStorageHandler'
TBLPROPERTIES
  (
    'format-version' = '2',
    'iceberg.mr.table.schema'='{"type":"struct","schema-id":0,"identifier-field-ids":[1],"fields":[{"id":1,"name":"id","required":true,"type":"int"},{"id":2,"name":"int_test","required":false,"type":"int"},{"id":3,"name":"bigint_test","required":false,"type":"long"},{"id":4,"name":"float_test","required":false,"type":"float"},{"id":5,"name":"double_test","required":false,"type":"double"},{"id":6,"name":"decimal_test","required":false,"type":"decimal(18, 2)"},{"id":7,"name":"date_test","required":false,"type":"date"},{"id":8,"name":"varchar_test","required":false,"type":"string"},{"id":9,"name":"datetime_test","required":false,"type":"timestamp"}]}',
    'engine.hive.enabled' = 'true',
    'external.table.purge' = 'TRUE',
    'table_type' = 'ICEBERG',
    'write.upsert.enabled' = 'true'
  );


CREATE TABLE `ics4`(
                       `id` INT NOT NULL,
                       `array_test` array<string>,
                       `map_test` map<string,string>,
                       `struct_test` struct<math:int,english:int>,
                       `bigint_test` bigint,
                       `binary_test` binary,
                       `boolean_test` boolean,
                       `date_test` date,
                       `decimal_test` decimal(18,2),
                       `double_test` double,
                       `float_test` float,
                       `varchar_test` string,
                       `string_test` string,
                       `timestamp_test` timestamp,
                       primary key (`id`) NOT ENFORCED
)
    STORED BY 'org.apache.iceberg.mr.hive.HiveIcebergStorageHandler'
    TBLPROPERTIES
        (
        'format-version' = '2',
        'iceberg.mr.table.schema'='{"type":"struct","schema-id":0,"identifier-field-ids":[1],"fields":[{"id":1,"name":"id","required":true,"type":"int"},{"id":2,"name":"array_test","required":false,"type":{"type":"list","element-id":15,"element":"string","element-required":false}},{"id":3,"name":"map_test","required":false,"type":{"type":"map","key-id":16,"key":"string","value-id":17,"value":"string","value-required":false}},{"id":4,"name":"struct_test","required":false,"type":{"type":"struct","fields":[{"id":18,"name":"math","required":false,"type":"int"},{"id":19,"name":"english","required":false,"type":"int"}]}},{"id":5,"name":"bigint_test","required":false,"type":"long"},{"id":6,"name":"binary_test","required":false,"type":"binary"},{"id":7,"name":"boolean_test","required":false,"type":"boolean"},{"id":8,"name":"date_test","required":false,"type":"date"},{"id":9,"name":"decimal_test","required":false,"type":"decimal(18, 2)"},{"id":10,"name":"double_test","required":false,"type":"double"},{"id":11,"name":"float_test","required":false,"type":"float"},{"id":12,"name":"varchar_test","required":false,"type":"string"},{"id":13,"name":"string_test","required":false,"type":"string"},{"id":14,"name":"timestamp_test","required":false,"type":"timestamp"}]}',
        'engine.hive.enabled' = 'true',
        'external.table.purge' = 'TRUE',
        'table_type' = 'ICEBERG',
        'write.upsert.enabled' = 'true'
        );


show create table ics1;

hive数据参考

hive字段类型建表插入数据-CSDN博客

Flinksql参考

-- 创建CATALOG
CREATE CATALOG `FLINK_CATALOG` WITH('type' = 'generic_in_memory');
-- 创建DATABASE
CREATE DATABASE IF NOT EXISTS `FLINK_CATALOG`.`FLINK_DATABASE`;


--   引用数据表:  [源表]
--   type: kafka
--   datasourceName: kf
--   tableName: hkcs
CREATE TABLE IF NOT EXISTS `FLINK_CATALOG`.`FLINK_DATABASE`.`hkcs` (
  `id` INT,
  proc_time AS PROCTIME()
) WITH (
  'connector' = 'kafka',
  'topic' = 'hkcs',
  'properties.bootstrap.servers' = 'hadoop54.xxx.net:9092,hadoop55.xxx.net:9092',
  'properties.group.id' = 'b9eb7741-083d-4b92-baef-b129cc619fae',
  'scan.startup.mode' = 'earliest-offset',
  'format' = 'json',
  'datasourceId' = '1807983506681262084'
);


-- 创建CATALOG
CREATE CATALOG ICEBERG_CATALOG WITH (
  'type' = 'iceberg',
  'catalog-type' = 'hive',
  'uri' = 'thrift://hadoop55.xxx.net:9083,thrift://hadoop56.xxx.net:9083',
  'clients' = '5',
  'property-version' = '2',
  'warehouse' = '/apps/hive/warehouse',
  'datasourceId' = '1807973902626877505'
);


--   引用数据表:  [结果表]
--   type: ICEBERG
--   datasourceName: ods
--   tableName: ics4
CREATE TABLE IF NOT EXISTS `ICEBERG_CATALOG`.`ods`.`ics4` (
  `id` INT,
  `array_test` ARRAY < STRING >,
  `map_test` MAP < STRING,
  STRING >,
  `struct_test` ROW < math INT,
  english INT >,
  `bigint_test` BIGINT,
  `binary_test` BINARY,
  `boolean_test` BOOLEAN,
  `date_test` DATE,
  `decimal_test` DECIMAL(18, 2),
  `double_test` DOUBLE,
  `float_test` FLOAT,
  `varchar_test` STRING,
  `string_test` STRING,
  `timestamp_test` TIMESTAMP,
  PRIMARY KEY (`id`) NOT ENFORCED
) WITH (
  'external.table.purge' = 'TRUE',
  'format-version' = '2',
  'engine.hive.enabled' = 'true',
  'table_type' = 'ICEBERG',
  'write.upsert.enabled' = 'true',
  'iceberg-database-name' = 'ods',
  'iceberg-table-name' = 'ics4',
  'datasourceId' = '1807973902626877505'
);


-- 创建CATALOG
CREATE CATALOG HIVE_CATALOG WITH (
  'type' = 'hive',
  'default-database' = 'default',
  'publish-database-name' = 'ods',
  'publish-table-name' = 'cshive',
  'datasourceId' = '1807973902626877505'
);


-- 切换hive方言
SET  table.sql-dialect = hive;

CREATE TABLE IF NOT EXISTS `HIVE_CATALOG`.`ods`.`cshive` (
    `int_test` INT,
    `smallint_test` SMALLINT,
    `varchar_test` VARCHAR(100),
    `array_test` ARRAY < STRING >,
    `map_test` MAP < STRING,
    STRING >,
    `struct_test` STRUCT < math :INT,
    english :INT >,
    `bigint_test` BIGINT,
    `binary_test` BINARY,
    `boolean_test` BOOLEAN,
    `char_test` CHAR(100),
    `date_test` DATE,
    `decimal_test` DECIMAL(18, 2),
    `double_test` DOUBLE,
    `float_test` FLOAT,
    `string_test` STRING,
    `timestamp_test` TIMESTAMP
  ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '|' MAP KEYS TERMINATED BY '#' STORED AS TEXTFILE LOCATION 'hdfs://nameservice1/apps/hive/warehouse/ods.db/cshive';

-- 切换flink方言
 SET  table.sql-dialect = default;


 INSERT INTO  `ICEBERG_CATALOG`.`ods`.`ics4`
 SELECT  a.`id`,
  b.`array_test`,
  b.`map_test`,
  b.`struct_test`,
  b.`bigint_test`,
  b.`binary_test`,
  b.`boolean_test`,
  b.`date_test`,
  b.`decimal_test`,
  b.`double_test`,
  b.`float_test`,
  b.`varchar_test`,
  b.`string_test`,
  b.`timestamp_test`
 from  `FLINK_CATALOG`.`FLINK_DATABASE`.`hkcs` a
  LEFT join `HIVE_CATALOG`.`ods`.`cshive` FOR SYSTEM_TIME AS OF a.proc_time b ON b.`int_test` = a.`id`