如何使用hive3的acid表实现kimball建模的代理键

如何使用hive3的acid表实现kimball建模的代理键

1.使用byzer导入2亿条数据

2.byzer将数据写入到hive3

3.在hive中check数据

走到这里条数也对上了

4.我们创建一张customer_test表，使用scd的方式，然后对这张2亿条数据的表进行重刷，查看时间

set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
drop table if exists test_event_acid;
CREATE TABLE `test_event_acid`(
  `customer_id` string, 
  `event` string, 
  `tenant_id` int
) STORED AS ORC TBLPROPERTIES(
'transactional'='true',
'transactional_properties'='default');
 INSERT INTO test_event_acid (customer_id, event, tenant_id) SELECT customer_id, event, tenant_id from test_event;
  
drop table if exists customer_test;
CREATE TABLE customer_test
(
 `ID` bigint not null,
 customer_id string,
 name VARCHAR(64), 
 PRIMARY KEY (ID) DISABLE NOVALIDATE
);

INSERT INTO customer_test (id, customer_id, name) SELECT id, customer_id ,'zhangsan' from test_event limit 100000;

ALTER TABLE test_event_acid ADD COLUMNS (c_id bigint);
 
MERGE INTO test_event_acid g USING customer_test s ON g.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET c_id = s.id;

5.合并时间98s