向ClickHouse表插入复杂数据

最新推荐文章于 2024-06-04 17:33:37 发布

原创最新推荐文章于 2024-06-04 17:33:37 发布 · 1.3k 阅读

12 ·

CC 4.0 BY-SA版权

本文为博主原创文章，未经博主允许不得转载。

文章标签：

#面试

clickhouse 列式存储专栏收录该内容

4 篇文章

订阅专栏

本文介绍了如何使用ClickHouse创建分布式与复制表，并展示了模拟数据及导入CSV格式数据的过程。还演示了如何插入数据以及删除特定日期的数据。此外，提供了相关技术博客链接供读者深入学习。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、创建表

CREATE TABLE test_db.aa_local on cluster cluster_name (
    fdate Int64,  
    time_slot Int64,  
    placement_id String,  
    city_id String,  
    city_name String,  
    user_id String,  
    request_id String,  
    request_time_ms Int64,  
    ad_id Nullable(String),  
    singerid_arr Array(String),  
    album_id String,  
    device_software_os String
  ) ENGINE = ReplicatedMergeTree(\'/clickhouse/tables/{shard}/test_db_aa_local\', \'{replica}\')
  PARTITION BY fdate ORDER BY (fdate, placement_id, time_slot, city_id, city_name) SETTINGS index_granularity = 8192

CREATE TABLE test_db.aa_all on cluster cluster_name (
   fdate Int64,  
   time_slot Int64,  
   placement_id String,  
   city_id String,  
   city_name String,  
   user_id String,  
   request_id String,  
   request_time_ms Int64,  
   ad_id Nullable(String),  
   singerid_arr Array(String),  
   album_id String,  
   device_software_os String
) ENGINE = Distributed(cluster_name, test_db, aa_all, time_slot)

2、模拟数据

aa_all_data.csv 数据

20210325,202103050700,102100000,1200,tianjin,1439127799,1452906114702139,145,1504,['1'],1,android
20210325,202103050700,102100000,1200,tianjin,1421962733,1485462913297402,144,1504,['1'],1,android
20210325,202103050700,102100000,1200,tianjin,1421962733,1485462913297402,144,1504,[‘1’,’2'],1,android

20210325,202103050700,102100000,1200,tianjin,1439127799,1452906114702139,145,1504,['1'],1,android
20210325,202103050700,102100000,1200,tianjin,1421962733,1485462913297402,144,1504,['1'],1,android
20210325,202103050700,102100000,1200,tianjin,1421962733,1485462913297402,144,1504,"['1','2']",1,android
20210325,202103050700,102100000,1200,tianjin,1421962733,1485462913297402,144,1504,"['1','2','3']",1,android

3、导入CSV格式数据

clickhouse-client -h 1.1.1.12 -format_csv_delimiter=',' -q 'insert into test_db.aa_all FORMAT CSV ' < /root/aa_all_data.csv

4、插入的方式导入数据

insert into aa_all values
    (20210312, 202103080802, '102100000', '1202', '深圳市',
    '001', '1111111111zzz', 1504, 2021, ['林俊杰11','宋祖英22'], '12347', 'linux');

5、删除数据

ALTER TABLE aa_local ON CLUSTER cluster_name DELETE WHERE fdate = 20210325;

文章最后，给大家推荐一些受欢迎的技术博客链接：

欢迎扫描下方的二维码或搜索公众号“大数据高级架构师”，我们会有更多、且及时的资料推送给您，欢迎多多交流！