OpenAi平台帐号
https://platform.openai.com/
uid:hdkingree@gmail.com
pwd:HuangDiKingree
apiKey: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


sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
sudo yum install -y clickhouse-server clickhouse-client


systemctl status clickhouse-server 
systemctl start clickhouse-server 
systemctl stop clickhouse-server 


systemctl restart clickhouse-server



chmod -R 777 [文件夹名称]
chmod 777 [文件/文件夹名称]
chmod 777 config.xml


clickhouse-client  --port=9002  -m 
clickhouse-client  -h hadoop03 --port=9002  -m 

clickhouse-client  --port=9001  -m  --queries-file /root/testClickHouse.sql 

clickhouse-client  --port=9001  -mn  --param_sevendayago=2022-12-19  --queries-file  /root/testClickHouse.sql 

clickhouse-client  --port=9001  -m  --param_sevendayago=2022-12-19  -q "ALTER TABLE shence.roi_kyy_now_channel_cpc DROP PARTITION ''2023-02-14''"

clickhouse-client  --port=9001  -m  --param_sevendayago=2022-12-19  -q "ALTER TABLE shence.roi_kyy_now_channel_cpc DROP PARTITION '2022-12-19'"


clickhouse-client  --port=9001  -m  --param_sevendayago=2022-12-19  -q "select '`'2023-02-14'`'"


SELECT table,partition,path from system.parts WHERE table = 'roi_kyy_now_channel_cpc'





函数
isFinite(x) //接受Float32或Float64类型的参数，如果参数不是infinite且不是NaN，则返回1，否则返回0。
isInfinite(x) //接受Float32或Float64类型的参数，如果参数是infinite，则返回1，否则返回0。注意NaN返回0。
isNaN(x) //接受Float32或Float64类型的参数，如果参数是Nan，则返回1，否则返回0。


语句
SHOW PROCESSLIST;//查询正在进行执行的sql操作

ALTER TABLE shence.roi_kyy_now_all_cpc  MODIFY COLUMN appname Nullable(String); //修改列可以为空

ALTER TABLE shence.roi_kyy_now_channel_cpc DROP PARTITION '2022-12-19';//删除分区数据

CREATE DATABASE IF NOT EXISTS test03 ON CLUSTER test_cluster_two_shards_internal_replication;//创建分布式数据库

DROP TABLE IF EXISTS test03.test_ali_behavior6 ON CLUSTER test_cluster_two_shards_internal_replication;//删除分布式数据表

ALTER TABLE shence.roi_kyy_now_channel_cpc_test ADD COLUMN app_name String AFTER cpc;//添加列放在指定列名之后

ALTER TABLE shence.roi_kyy_now ADD COLUMN appname String ; //添加列

ALTER TABLE shence.roi_app_now_user DROP COLUMN active_user; //删除列





select * from system.clusters;
SELECT version();
select * FROM system.databases;
select * FROM system.zookeeper;


SELECT
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_uncompressed_bytes) / sum(data_compressed_bytes)), 0) AS `压缩率`,
    `table` AS `表名`
FROM system.parts  where database = 'shence'  group by `table`;






create table IF NOT EXISTS test_db.roi_kyy_now_click_show (
  cnid   String     comment '',
  adunitid   String     comment '',
  manufacturer      String     comment '',
  ad_show    UInt64       comment '',
  ad_click   UInt64       comment '',
  dt          String     comment '',
  source          String     comment '',
  appname          String     comment ''
)
engine= MergeTree()
ORDER BY (dt)


CREATE TABLE business.roi_kyy_hour
(
    `cnid` String,
    `hour` Int64,
    `manufacturer` String,
    `pull_user` Int64,
    `cost_avg` Float64,
    `ad_show_avg` Float64,
    `ctr` Float64,
    `cpc` Float64,
    `arpu` Float64,
    `gross_margin` Float64,
    `roi` Float64,
    `cdt` String
)
ENGINE = MySQL('dbmaster-h5-hadoop.cread.com:3312', 'ikanshu_common', 'roi_kyy_hour', 'idc_hadoop_w', 'Rs2EkMiTMMCjU')
SETTINGS read_write_timeout=10000, connect_timeout=100;






























create table IF NOT EXISTS test03.roi_kyy_now (
  cnid   String     comment '',
  manufacturer      String     comment '',
  pull_user      String      comment '',
  income    String      comment '',
  ad_show    String      comment '',
  ad_click   String      comment '',
  cost          String      comment '',
  cdt          String     comment ''
)
engine=HDFS('hdfs://cmNameNodeHA/shence/roi_kyy_now/dt=*/*','TSV')
PARTITION BY cdt;


create table IF NOT EXISTS test03.roi_kyy_now1 (
  cnid   String     comment '',
  manufacturer      String     comment '',
  pull_user      UInt64      comment '',
  income    Float64      comment '',
  ad_show    UInt64      comment '',
  ad_click   UInt64      comment '',
  cost          Float64      comment '',
  cdt          String     comment '',
  dt          String     comment ''
)
engine=Hive('thrift://spark1:9083','shence','roi_kyy_now')
PARTITION BY dt;