clickhouse select结果创建表,insert 现有表

这篇博客介绍了如何使用ClickHouse进行数据处理,包括将查询结果插入现有表`company_weibo_old`以及根据查询结果创建新表`company_weibo_new`。查询条件是`chuli_date='2021-11-16 00:00:00'`,涉及的字段包括ID、CID、公司名称等多个信息字段。
clickhouse查询结果创建表,insert 现有表

1、查询结果insert 现有表

INSERT INTO company_weibo_old 
(ID, CID, COMPANY_NAME, NAME, ICO, INFO, TAGS, COUNT, FANS, FOLLOW_COUNT, SOURCE_URL, CREATE_TIME, UPDATE_TIME, DELETED, ROW_TYPE, CHULI_DATE
<think> 我们正在讨论如何通过SELECT语句在ClickHouse创建。根据引用[1]中的示例,我们可以使用`CREATE TABLE ... ENGINE = ... AS SELECT ...`的语法结构。用户之前的问题是关于插入分区,但现在转向了通过SELECT创建,因此我们需要重点解释这个语法。 在ClickHouse中,通过SELECT语句创建主要有两种方式: 1. 使用`CREATE TABLE ... AS SELECT ...`(CTAS)语句 2. 使用`CREATE TABLE ... ENGINE = ... AS SELECT ...`(指定引擎) 由于ClickHouse要求创建时必须指定引擎(因为引擎决定了的存储方式和特性),所以通常我们需要在CREATE TABLE语句中指定引擎。 引用[1]中的示例是: ```sql CREATE TABLE db_admin.role ENGINE = MergeTree ORDER BY id AS select * from db_admin.role ``` 注意:这个示例实际上是从同一个数据库的同一张复制数据,但通常我们也可以从不同的或查询结果创建。 具体步骤: 1. 创建,并指定引擎(如MergeTree家族)和必要的引擎参数(如排序键ORDER BY)。 2. 使用AS子句,后面跟上SELECT查询,该查询的结果将作为新的初始数据。 但是,需要注意的是,CTAS语句在创建时,结构将基于SELECT查询的结果集的结构(包括列名和类型),同时还可以通过ENGINE子句指定存储引擎和索引结构。 详细语法: ```sql CREATE TABLE [IF NOT EXISTS] [database.]table_name [ON CLUSTER cluster] ( ... ) ENGINE = engine_name() [PARTITION BY ...] [ORDER BY ...] [PRIMARY KEY ...] [SETTINGS ...] [AS SELECT ...]; ``` 或者简写形式(当结构与SELECT结果完全一致,且不需要在名后面列出列定义时): ```sql CREATE TABLE [database.]table_name [ON CLUSTER cluster] ENGINE = engine_name() [PARTITION BY ...] [ORDER BY ...] [PRIMARY KEY ...] [SETTINGS ...] AS SELECT ... ``` 注意:在简写形式中,结构(列名和类型)由SELECT语句决定,因此SELECT语句中的列名和类型必须是明确的。 示例1:从现有创建(复制结构和数据) ```sql CREATE TABLE new_table ENGINE = MergeTree ORDER BY id AS SELECT * FROM original_table; ``` 示例2:创建一个新,只包含原始中的部分列和行 ```sql CREATE TABLE sampled_data ENGINE = MergeTree ORDER BY id AS SELECT id, name, event_date FROM original_table WHERE event_date > '2020-01-01'; ``` 示例3:使用分布式引擎创建分布式(需要集群配置) ```sql CREATE TABLE distributed_table ON CLUSTER my_cluster ENGINE = Distributed(my_cluster, currentDatabase(), local_table, rand()) AS SELECT ... FROM ...; ``` 注意:在分布式上使用CTAS需要谨慎,因为分布式本身不存储数据,所以通常我们创建的是本地。 重要提示: - 使用CTAS创建时,必须确保SELECT语句返回的列名和类型是确定的,不要使用`SELECT *`除非你明确知道源结构且不会改变。 - 新的结构(列名和类型)由SELECT查询的结果决定,所以在创建时可以省略列定义部分。 - 如果需要在创建时指定分区、索引等,必须在ENGINE子句中一并指定(如PARTITION BY, ORDER BY等)。 但是,如果希望创建结构与SELECT结果不完全相同(比如想添加额外的列或修改列类型),则不能使用简写形式,而需要在名后面显式定义列,然后在SELECT语句中通过别名匹配: ```sql CREATE TABLE explicit_table ( id UInt32, name String, date Date ) ENGINE = MergeTree PARTITION BY toYYYYMM(date) ORDER BY id AS SELECT id, name, event_date AS date -- 这里使用别名匹配定义中的列名 FROM original_table; ``` 性能考虑: - 数据写入:CTAS语句执行时,SELECT查询的结果将直接写入新,所以数据量大的情况下可能会耗时较长。 - 原子性:创建是原子的,如果创建过程中失败,则不会留下不完整的。 另外,还可以创建(不复制数据): ```sql CREATE TABLE empty_table ENGINE = MergeTree ORDER BY id AS SELECT * FROM original_table LIMIT 0; ``` 最后,关于分区:在CTAS语句中,可以在创建时指定分区键(PARTITION BY),这样创建就是分区。插入的数据会根据分区键自动分区存储。 示例:创建一个分区 ```sql CREATE TABLE partitioned_table ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (id, event_date) AS SELECT id, event_date, name FROM original_table; ``` 注意事项:分区键必须是SELECT结果中的列,且在引擎参数中指定。 以上即为ClickHouse中使用SELECT语句创建的方法。 现在,根据要求,我需要生成相关问题。 </think> ### ClickHouse 通过 SELECT 语句创建详解 在 ClickHouse 中,通过 `SELECT` 语句创建主要使用 **`CREATE TABLE ... AS SELECT ...`(CTAS)** 语法,这是一种高效的结构和数据复制方式。以下是核心方法和示例: #### 一、基础语法结构 ```sql CREATE TABLE [IF NOT EXISTS] [database.]table_name ENGINE = engine_name() [PARTITION BY expr] [ORDER BY expr] [PRIMARY KEY expr] [SETTINGS ...] AS SELECT ... ``` - **`ENGINE`**:必须指定存储引擎(常用 `MergeTree` 系列) - **`AS SELECT`**:定义结构和初始数据来源 - **`IF NOT EXISTS`**:避免已存在时报错 #### 二、三种典型场景示例 1. **复制结构和数据(全量克隆)** ```sql -- 创建并复制所有数据 CREATE TABLE new_table ENGINE = MergeTree ORDER BY id AS SELECT * FROM source_table; ``` 2. **创建带分区的** ```sql -- 创建分区并导入筛选数据 CREATE TABLE partitioned_sales ENGINE = MergeTree PARTITION BY toYYYYMM(order_date) -- 按月分区 ORDER BY (product_id, order_date) AS SELECT product_id, order_date, quantity * price AS total_sales FROM source_orders WHERE order_date > '2023-01-01'; -- 过滤条件 ``` 3. **仅复制结构(空)** ```sql -- 创建(保留数据结构) CREATE TABLE empty_clone ENGINE = MergeTree ORDER BY id AS SELECT * FROM source_table LIMIT 0; -- 关键:LIMIT 0 ``` #### 三、技术要点说明 1. **数据类型映射** SELECT 查询结果的列名和数据类型自动成为新的定义: ```sql CREATE TABLE user_summary ENGINE = MergeTree ORDER BY user_id AS SELECT user_id, count() AS total_orders, -- UInt64 类型 avg(amount) AS avg_amount -- Float64 类型 FROM orders GROUP BY user_id; ``` 2. **引擎选择策略** | **场景** | **推荐引擎** | **示例** | |------------------|-------------------|----------------------------------| | 高频查询 | `ReplacingMergeTree` | `ENGINE = ReplacingMergeTree()` | | 分布式部署 | `ReplicatedMergeTree` | `ENGINE = ReplicatedMergeTree(...)` | | 日志类数据 | `StripeLog` | `ENGINE = StripeLog()` | 3. **分布式创建** ```sql -- 在集群中创建本地+分布式 CREATE TABLE sharded.cluster_data_local ON CLUSTER my_cluster ENGINE = ReplicatedMergeTree('/tables/{shard}/data', '{replica}') ORDER BY timestamp AS SELECT ... FROM source; CREATE TABLE sharded.cluster_data_all ON CLUSTER my_cluster ENGINE = Distributed('my_cluster', 'sharded', 'cluster_data_local', rand()) AS SELECT * FROM sharded.cluster_data_local; -- 可选初始化数据 ``` #### 四、性能优化建议 1. **内存控制** 处理大数据时添加限制: ```sql CREATE TABLE large_table ENGINE = MergeTree ORDER BY id AS SELECT * FROM huge_source LIMIT 1000000 BY 1; -- 分批处理 ``` 2. **分区裁剪** WHERE 条件提前过滤数据: ```sql CREATE TABLE recent_users ENGINE = MergeTree PARTITION BY city ORDER BY signup_date AS SELECT * FROM users WHERE signup_date > now() - INTERVAL 30 DAY; -- 减少处理量 ``` 3. **轻量引擎初始化** 先创空再插入数据: ```sql CREATE TABLE stage ENGINE = Memory AS SELECT ... LIMIT 0; INSERT INTO target_table SELECT * FROM stage; DROP TABLE stage; -- 释放内存 ``` #### 五、常见错误处理 | **错误类型** | **解决方案** | |--------------------------|----------------------------------| | `DB::Exception: Memory limit exceeded` | 减小批次大小或增加 `max_memory_usage` | | `Mismatch column count` | 检查 SELECT 列与目标引擎兼容性 | | `Unknown identifier` | 确认源字段存在且名称正确 | > 注:CTAS 操作具有原子性,失败时不会残留部分数据[^1]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值