1、创建数据库
1、语法
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = engine
2、案例
db01 :) create database t2 engine=Atomic;
CREATE DATABASE t2
ENGINE = Atomic
Query id: c3344686-eeae-4782-8fe1-24f3b74cb225
Ok.
0 rows in set. Elapsed: 0.003 sec.
CREATE DATABASE mv_postgres
ENGINE = MaterializedPostgreSQL('172.16.220.10:5432', 'test', 'cy', 'P@ssw0rd')
SETTINGS materialized_postgresql_max_block_size = 65536,
materialized_postgresql_tables_list = 't1';
db01 :) drop database t2;
DROP DATABASE t2
Query id: c8117727-0784-46e0-acbf-572f61156462
Ok.
0 rows in set. Elapsed: 0.001 sec.
2、建表
1、语法
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1] [COMMENT 'comment for column'],
name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2] [COMMENT 'comment for column'],
...
) ENGINE = engine
COMMENT 'comment for table'
CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]
CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...
2、案例
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
db01 :) create database to_mysql engine=MySQL('172.16.220.10:3306','test','cy','P@ssw0rd');
CREATE DATABASE to_mysql
ENGINE = MySQL('172.16.220.10:3306', 'test', 'cy', 'P@ssw0rd')
Query id: 40b29856-bb0d-4329-9568-d775faa3493c
Ok.
0 rows in set. Elapsed: 0.010 sec.
db01 :) use to_mysql;
USE to_mysql
Query id: bca71829-9169-4428-bf44-159621b44900
Ok.
0 rows in set. Elapsed: 0.001 sec.
db01 :) show tables;
SHOW TABLES
Query id: ce48eb19-b4df-4c18-857d-79900479937e
┌─name─┐
│ t1 │
└──────┘
1 row in set. Elapsed: 0.005 sec.
db01 :) select * from t1;
SELECT *
FROM t1
Query id: 5f1ae65c-4a6f-44ab-a4e9-9f7a50178d23
┌─id─┬─name─┐
│ 3 │ wz │
│ 4 │ szs │
│ 1 │ yz │
│ 6 │ gy │
│ 7 │ yn │
│ 8 │ yc │
│ 5 │ ww │
└────┴──────┘
7 rows in set. Elapsed: 0.011 sec.
db01 :) create table t3(id UInt64,name String) engine=MergeTree() order by id as select * from to_mysql.t1;
CREATE TABLE t3
(
`id` UInt64,
`name` String
)
ENGINE = MergeTree
ORDER BY id AS
SELECT *
FROM to_mysql.t1
Query id: 12f3b993-de53-4424-aeb9-0eacd5556b07
Ok.
0 rows in set. Elapsed: 0.017 sec.
db01 :) select * from t3;
SELECT *
FROM t3
Query id: 975f871d-271c-471f-b40a-8876ee86a505
┌─id─┬─name─┐
│ 1 │ yz │
│ 3 │ wz │
│ 5 │ ww │
│ 6 │ gy │
│ 7 │ yn │
│ 8 │ yc │
│ 9 │ dj │
└────┴──────┘
7 rows in set. Elapsed: 0.003 sec.
CREATE DATABASE to_postgres
ENGINE = PostgreSQL('172.16.220.10:5432', 'test', 'cy', 'P@ssw0rd', 'public');
db01 :) show tables;
SHOW TABLES
Query id: 992261c0-e3f1-40d9-9a1a-036ad9693560
Ok.
0 rows in set. Elapsed: 0.012 sec.
db01 :) select * from t1;
SELECT *
FROM t1
Query id: 9bb80bfd-5191-40c2-a45e-2c19d6b3c864
Elapsed: 0.004 sec.
Received exception from server (version 23.12.2):
Code: 36. DB::Exception: Received from localhost:9000. DB::Exception: PostgreSQL array cannot be NULL: "public"."t1"."enumvals": while fetching postgresql table structure. (BAD_ARGUMENTS)
不能为null，有点鬼
db01 :) show tables;
SHOW TABLES
Query id: 94e16b36-e50d-4849-aa72-a24e4de30748
┌─name─┐
│ t1 │
└──────┘
1 row in set. Elapsed: 0.007 sec.
3、查看ddl
db01 :) show create table t3;
SHOW CREATE TABLE t3
Query id: 15de53dc-681d-467e-be71-98afc78002af
┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE t1.t3
(
`id` UInt64,
`name` String
)
ENGINE = MergeTree
ORDER BY id
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 row in set. Elapsed: 0.001 sec.
4、表函数创建表
db01 :) create table t1.sales(prod_id UInt32,cust_id UInt32,time_id date,channel_id UInt32,promo_id UInt32,quantity_sold UInt32,amount_sold Float64) engine=File(CSV);
CREATE TABLE t1.sales
(
`prod_id` UInt32,
`cust_id` UInt32,
`time_id` date,
`channel_id` UInt32,
`promo_id` UInt32,
`quantity_sold` UInt32,
`amount_sold` Float64
)
ENGINE = File(CSV)
Query id: 6f65f95f-4789-4209-b6ab-d4b41821da99
Ok.
0 rows in set. Elapsed: 0.003 sec.
[root@db01 t1]# cd /var/lib/clickhouse/data/t1/sales/
[root@db01 sales]# ls
[root@db01 sales]# cd ..
[root@db01 t1]# ls -lsa
total 0
0 drwxr-x--- 2 clickhouse clickhouse 87 Jan 20 21:12 .
0 drwxr-x--- 7 clickhouse clickhouse 80 Jan 20 20:50 ..
0 lrwxrwxrwx 1 clickhouse clickhouse 67 Jan 20 21:12 sales -> /var/lib/clickhouse/store/c84/c8487945-3391-4ecc-b631-8866a426b237/
0 lrwxrwxrwx 1 clickhouse clickhouse 67 Jan 20 18:25 stripe_log_table -> /var/lib/clickhouse/store/a4e/a4e036c1-c423-4d71-bd6a-de16408fc302/
0 lrwxrwxrwx 1 clickhouse clickhouse 67 Jan 20 18:17 summtt -> /var/lib/clickhouse/store/988/988f13af-651b-4e6c-aae6-b432067304a1/
0 lrwxrwxrwx 1 clickhouse clickhouse 67 Jan 20 17:40 t1 -> /var/lib/clickhouse/store/ab9/ab9b630f-5917-4689-b464-9c878fb36f2e/
0 lrwxrwxrwx 1 clickhouse clickhouse 67 Jan 20 20:54 t2 -> /var/lib/clickhouse/store/7b1/7b1c9d9f-3703-4a97-8aca-03c248a5493b/
0 lrwxrwxrwx 1 clickhouse clickhouse 67 Jan 20 20:57 t3 -> /var/lib/clickhouse/store/026/0265b59a-73db-4705-8957-a56a17e7d4db/
[root@db01 t1]# pwd
/var/lib/clickhouse/data/t1
[root@db01 t1]# ls
sales stripe_log_table summtt t1 t2 t3
[root@db01 t1]# cp /root/sales.csv sales/
db01 :) use t1;
USE t1
Query id: d79d2a3b-aec6-4b53-95f0-f375e53fa34c
Ok.
0 rows in set. Elapsed: 0.003 sec.
db01 :) show tables;
SHOW TABLES
Query id: 320a11c8-24bb-4b19-8a7d-fe7d3ddc77f8
┌─name─────────────┐
│ sales │
│ stripe_log_table │
│ summtt │
│ t1 │
│ t2 │
│ t3 │
└──────────────────┘
6 rows in set. Elapsed: 0.002 sec.
db01 :) select count(*) from sales;
SELECT count(*)
FROM sales
Query id: 5e96d7c2-8d9d-4d3f-b787-e6a5e74ef8da
┌─count()─┐
│ 918844 │
└─────────┘
1 row in set. Elapsed: 0.113 sec. Processed 918.84 thousand rows, 47.47 MB (8.15 million rows/s., 421.22 MB/s.)
Peak memory usage: 100.55 KiB.
│ 31 │ 6612 │ 1998-02-21 │ 3 │ 999 │ 1 │ 9.74 │
│ 31 │ 6905 │ 1998-02-21 │ 3 │ 999 │ 1 │ 9.74 │
│ 31 │ 7013 │ 1998-02-21 │ 3 │ 999 │ 1 │ 9.74 │
│ 31 │ 11517 │ 1998-03-26 │ 2 │ 999 │ 1 │ 9.74 │
│ 31 │ 18125 │ 1998-03-26 │ 2 │ 999 │ 1 │ 9.74 │
│ 31 │ 615 │ 1998-03-26 │ 3 │ 999 │ 1 │ 9.74 │
│ 31 │ 5015 │ 1998-03-26 │ 3 │ 999 │ 1 │ 9.74 │
│ 31 │ 6674 │ 1998-03-26 │ 3 │ 999 │ 1 │ 9.74 │
└─────────┴─────────┴────────────┴────────────┴──────────┴───────────────┴─────────────┘
Showed first 10000.
918843 rows in set. Elapsed: 0.737 sec. Processed 918.84 thousand rows, 27.57 MB (1.25 million rows/s., 37.41 MB/s.)
Peak memory usage: 30.67 MiB.
db01 :) select sum(amount_sold) from t1.sales;
SELECT sum(amount_sold)
FROM t1.sales
Query id: 55d2084a-a45f-4bdc-9d71-f995185e9d2f
┌──sum(amount_sold)─┐
│ 98205831.20999974 │
└───────────────────┘
1 row in set. Elapsed: 0.128 sec. Processed 918.84 thousand rows, 7.35 MB (7.18 million rows/s., 57.47 MB/s.)
Peak memory usage: 23.26 MiB.
db01 :) select count(*) from remote('172.16.220.10:9000',t1.sales);
SELECT count(*)
FROM remote('172.16.220.10:9000', t1.sales)
Query id: 74705ffd-fbed-4ff4-a4b3-b4b1a01f6af1
┌─count()─┐
│ 918843 │
└─────────┘
1 row in set. Elapsed: 0.004 sec. Processed 918.84 thousand rows, 150.00 B (214.77 million rows/s., 35.06 KB/s.)
Peak memory usage: 87.00 KiB.
3、数据控制
1、创建用户
CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1]
[, name2 [ON CLUSTER cluster_name2] ...]
[NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']} | {WITH ssl_certificate CN 'common_name'} | {WITH ssh_key BY KEY 'public_key' TYPE 'ssh-rsa|...'} | {WITH http SERVER 'server_name' [SCHEME 'Basic']}]
[HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
[VALID UNTIL datetime]
[IN access_storage_type]
[DEFAULT ROLE role [,...]]
[DEFAULT DATABASE database | NONE]
[GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]
密码复杂度需要在config.xml中编辑
<clickhouse>
<password_complexity>
<rule>
<pattern>.{12}</pattern>
<message>be at least 12 characters long</message>
</rule>
<rule>
<pattern>\p{N}</pattern>
<message>contain at least 1 numeric character</message>
</rule>
</password_complexity>
</clickhouse>
db01 :) create user cy not identified;
CREATE USER cy NOT IDENTIFIED
Query id: 4acdd23c-bc8b-45d1-97af-adfad597876e
Ok.
0 rows in set. Elapsed: 0.001 sec.
db01 :) CREATE USER cy IDENTIFIED WITH sha256_password BY 'oracle';
CREATE USER cy IDENTIFIED WITH sha256_password BY 'oracle'
Query id: 4d36e5b9-b966-46ad-8d0d-7a80a4282a8d
Ok.
0 rows in set. Elapsed: 0.001 sec.
db01 :) CREATE user cy HOST IP '172.16.220.10' IDENTIFIED WITH sha256_password BY 'oracle';
CREATE USER cy IDENTIFIED WITH sha256_password BY 'oracle' HOST IP '172.16.220.10'
Query id: 2c5ecc87-47f9-4194-84ee-3b4ef3d457bf
Ok.
0 rows in set. Elapsed: 0.001 sec.
2、授权
GRANT [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} TO {user | role | CURRENT_USER} [,...] [WITH GRANT OPTION] [WITH REPLACE OPTION]
db01 :) grant select on *.* to cy;
GRANT SELECT ON *.* TO cy
Query id: e32dc092-685d-4be3-8898-b4bab72f03a6
Ok.
0 rows in set. Elapsed: 0.001 sec.
db01 :) select * from system.users format Vertical;
SELECT *
FROM system.users
FORMAT Vertical
Query id: ee524dbb-e64b-4faf-826a-5dca4b59d01e
Row 1:
──────
name: default
id: 94309d50-4f52-5250-31bd-74fecac179db
storage: users_xml
auth_type: plaintext_password
auth_params: {}
host_ip: ['::/0']
host_names: []
host_names_regexp: []
host_names_like: []
default_roles_all: 1
default_roles_list: []
default_roles_except: []
grantees_any: 1
grantees_list: []
grantees_except: []
default_database:
Row 2:
──────
name: cy
id: 8747f0bf-5d92-71d2-53ea-27e436293197
storage: local_directory
auth_type: sha256_password
auth_params: {}
host_ip: ['172.16.220.10']
host_names: []
host_names_regexp: []
host_names_like: []
default_roles_all: 1
default_roles_list: []
default_roles_except: []
grantees_any: 1
grantees_list: []
grantees_except: []
default_database:
2 rows in set. Elapsed: 0.002 sec.
4、查看执行计划
1、语法
EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [setting = value, ...]
[
SELECT ... |
tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
]
[FORMAT ...]
2、测试
db01 :) explain select count(*) from t1.sales;
EXPLAIN
SELECT count(*)
FROM t1.sales
Query id: 347ea056-a13f-40eb-b69e-d76b784c69db
┌─explain─────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ ReadFromStorage (File) │
└─────────────────────────────────────────────┘
4 rows in set. Elapsed: 0.003 sec.
db01 :) explain select count(*) from t1;
EXPLAIN
SELECT count(*)
FROM t1
Query id: ba8cec1a-79d5-4ca5-a7b0-874b6d97a955
┌─explain──────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│ MergingAggregated │
│ ReadFromPreparedSource (Optimized trivial count) │
└──────────────────────────────────────────────────────┘
3 rows in set. Elapsed: 0.002 sec.
5、通过集成驱动获取oracle数据
[root@db01 sales]# yum install openssl unixODBC
[root@db01 sales]# yum install openssl libiodbc
1、安装oracle ODBC驱动
unzip instantclient-basic-linux.x64-19.21.0.0.0dbru.zip
unzip instantclient-odbc-linux.x64-19.21.0.0.0dbru.zip
2、编辑ODBC配置
$ vi ~/.bashrc
export LD_LIBRARY_PATH=/root/instantclient_19_21/:$LD_LIBRARY_PATH
cd /root/instantclient_19_21/
midr etc
cp ~/.odbc.ini etc/odbc.ini
cp /etc/odbcinst.ini etc/.
[root@db01 instantclient_19_21]# ./odbc_update_ini.sh .
*** ODBCINI environment variable not set,defaulting it to HOME directory!
没有设置ODBCINI环境变量，就直接放在home下了
完整配置方案
https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/odbc-driver.html#GUID-CD847FD4-7CF9-40E1-B0C0-6B9EB372E122
[root@db01 ~]# cat /etc/odbcinst.ini
[oracle]
[Oracle 19 ODBC driver]
Description = Oracle ODBC driver for Oracle 19
Driver = /root/instantclient_19_21/libsqora.so.19.1
Setup =
FileUsage =
CPTimeout =
CPReuse =
/root/instantclient_19_21/network/admin
[root@db01 admin]# cat tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.220.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
[root@db01 ~]# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
检查驱动
[root@db01 instantclient_19_21]# odbcinst -q -d
[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]
[Oracle 19 ODBC driver]
检查DSN
[root@db01 instantclient_19_21]# odbcinst -q -s -n OracleODBC-19
[OracleODBC-19]
AggregateSQLType=FLOAT
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
BindAsFLOAT=F
CacheBufferSize=20
CloseCursor=F
DisableDPM=F
DisableMTS=T
DisableRULEHint=T
Driver=Oracle 19 ODBC driver
DSN=OracleODBC-19
EXECSchemaOpt=
EXECSyntax=T
Failover=T
FailoverDelay=10
FailoverRetryCount=10
FetchBufferSize=64000
ForceWCHAR=F
LobPrefetchSize=8192
Lobs=T
Longs=T
MaxLargeData=0
MaxTokenSize=8192
MetadataIdDefault=F
QueryTimeout=T
ResultSets=T
ServerName=ORCL
SQLGetData extensions=F
SQLTranslateErrors=F
StatementCache=F
Translation DLL=
Translation Option=0
UseOCIDescribeAny=F
ServerName=ORCL
UserID=sh
Password=oracle
测试
[root@db01 instantclient_19_21]# isql -v OracleODBC-19
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
3、建表获取数据
db01 :) create table example.sales(PROD_ID Float64,CUST_ID Float64,TIME_ID Date,CHANNEL_ID Float64,PROMO_ID Float64,QUANTITY_SOLD Float64,AMOUNT_SOLD Float64) engine=ODBC('OracleODBC-19','orcl','sales');
CREATE TABLE example.sales
(
`PROD_ID` Float64,
`CUST_ID` Float64,
`TIME_ID` Date,
`CHANNEL_ID` Float64,
`PROMO_ID` Float64,
`QUANTITY_SOLD` Float64,
`AMOUNT_SOLD` Float64
)
ENGINE = ODBC('OracleODBC-19', 'orcl', 'sales')
Query id: e0435909-46a6-4ce6-b18e-5edef533e07c
Ok.
0 rows in set. Elapsed: 0.004 sec.
db01 :) show tables;
SHOW TABLES
Query id: e8480a82-fe16-4081-8da8-039e15993822
┌─name──┐
│ sales │
└───────┘
1 row in set. Elapsed: 0.002 sec.
最后修改时间：2024-01-21 16:09:50
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
