Doris常用语句

SWITCH 	mysql_report;
SWITCH 	mysql_floweda;
SWITCH 	mysql_base;
SWITCH 	internal;

select * from mysql_floweda.flow_eda.eda_flow;

select * from mysql_report.cz_report.etl_flow;
select * from mysql_report.cz_report.etl_flow_log;


#外部数据物化视图
create materialized view fruit_uv as select data_month,data_day,fruit from mysql_floweda.test.fruit_sale  group by data_month,data_day,fruit;


select * from mysql_base.sc_base.base_user;
#数据一
SWITCH 	mysql_floweda;
use flow_eda;
select * from eda_flow;
select * from eda_flow_node_data;
select * from test.fruit_sale;

#数据一
SWITCH 	mysql_report;
use cz_report;
select * from etl_flow_log;
select * from yshop_cloud_nacos;

desc mysql_report.cz_report.etl_flow_log all;

show columns from  mysql_report.cz_report.etl_flow_log

use cz_report;

select version()

 SHOW DATABASES;
  SHOW CATALOGS
	show create CATALOG mysql_report;
	drop  CATALOG mysql_base;
	
	


show create CATALOG mysql_floweda;
drop CATALOG mysql_floweda;
CREATE CATALOG `mysql_floweda` PROPERTIES (
"user" = "root",
"type" = "jdbc",
"password" = "123456",
"jdbc_url" = "jdbc:mysql://192.168.3.167:3306/flow_eda?yearIsDateType=false&tinyInt1isBit=false&transformedBitIsBoolean=true&useUnicode=true&rewriteBatchedStatements=true&characterEncoding=utf-8",
"driver_url" = "mysql-connector-java-5.1.39.jar",
"driver_class" = "com.mysql.jdbc.Driver"
);

CREATE CATALOG mysql_report PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="YX@86223886",
    "jdbc_url" = "jdbc:mysql://192.168.3.27:3306/cz_report",
    "driver_url" = "mysql-connector-j-8.0.32.jar",
    "driver_class" = "com.mysql.cj.jdbc.Driver"
)

CREATE CATALOG `mysql_base` PROPERTIES (
"user" = "root",
"type" = "jdbc",
"password" = "YX@86223886",
"jdbc_url" ="jdbc:mysql://192.168.3.27:3306/sc_base?yearIsDateType=false&tinyInt1isBit=false&transformedBitIsBoolean=true&useUnicode=true&rewriteBatchedStatements=true&characterEncoding=utf-8",
"driver_url" = "mysql-connector-j-8.0.32.jar",
"driver_class" = "com.mysql.cj.jdbc.Driver"
);

flush hosts;
show variables like "max_connection%";
flush hosts
select  flush-hosts
mysqladmin  -u  root  -p  flush-hosts





explain SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;

create table advertiser_view_record(time date, advertiser varchar(10), channel varchar(10), user_id int) distributed by hash(time) properties("replication_num" = "1");
insert into advertiser_view_record values("2020-02-02",'a','a',1);
insert into advertiser_view_record values("2020-02-02",'a','b',1);
insert into advertiser_view_record values("2020-02-02",'a','b',2);

insert into advertiser_view_record values("2020-02-01",'a','a',1);
insert into advertiser_view_record values("2020-02-01",'a','b',1);
insert into advertiser_view_record values("2020-02-01",'a','b',2);
insert into advertiser_view_record values("2020-02-01",'a','b',3);
desc advertiser_view_record;
select * from advertiser_view_record

SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record  GROUP BY advertiser, channel;
explain SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record  GROUP BY advertiser, channel;
# 创建物化视图 聚合
create materialized view advertiser_uv as select advertiser, channel, bitmap_union(to_bitmap(user_id)) from advertiser_view_record group by advertiser, channel;
# 创建物化视图 筛选+聚合
create materialized view advertiser_uv1 as select advertiser, channel, bitmap_union(to_bitmap(user_id)) from advertiser_view_record  where time = '2020-02-01' group by advertiser, channel;
create materialized view advertiser_uv_time as select time, advertiser, channel, bitmap_union(to_bitmap(user_id)) from advertiser_view_record   group by  time, advertiser, channel;
#聚合
explain SELECT channel, count(distinct user_id) FROM advertiser_view_record  GROUP BY channel;
explain SELECT advertiser, count(distinct user_id) FROM advertiser_view_record where channel = "b" GROUP BY advertiser;

#筛选+聚合
explain SELECT advertiser, count(distinct user_id) FROM advertiser_view_record where GROUP BY advertiser;
explain SELECT advertiser, count(distinct user_id) FROM advertiser_view_record where time = '2020-02-01' GROUP BY  advertiser, channel;
explain SELECT advertiser, count(distinct user_id) FROM advertiser_view_record where time = '2020-02-01' GROUP BY  advertiser;
explain SELECT advertiser,  count(user_id) FROM advertiser_view_record where time = '2020-02-01' GROUP BY  advertiser;
explain SELECT advertiser FROM advertiser_view_record where time = '2020-02-02' 

explain select advertiser,  bitmap_union(to_bitmap(user_id)) FROM advertiser_view_record where time = '2020-02-02' GROUP BY advertiser;
explain select advertiser, channel,count(distinct user_id) from advertiser_view_record    group by advertiser, channel;
SET enable_nereids_planner=true;

desc advertiser_view_record all;

SWITCH 	mysql_floweda;
# 获取表数据和物化视图数据 行数等
show  data from iot_df_dataSet
show  data from internal.demo
SHOW  TABLES FROM demo;
SHOW  full columnS FROM iot_df_dataSet;
select * from iot_df_dataSet

show create table iot_df_dataSet

CREATE TABLE `iot_df_dataSet` (
  `type_id` varchar(50) NOT NULL COMMENT '类型id',
  `service_id` varchar(50) NOT NULL COMMENT '该机器id',
  `sn` varchar(50) NOT NULL COMMENT '设备id',
  `_time` varchar(30) NULL COMMENT '数据集时间戳',
  `data_json` text NOT NULL COMMENT '数据集内容'
) ENGINE=OLAP
DUPLICATE KEY(`type_id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`type_id`, `service_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);


SHOW ENGINES
select * from advertiser_view_record



#创建表1
CREATE TABLE `create_test` (id int COMMENT "id",
                           seri_id BIGINT COMMENT "序列id",
                           price  DECIMAL(5,2) COMMENT "价格",
                           name  varchar(50) COMMENT "名称",
                           alis  String COMMENT "别名",
                           datetime  DATETIME COMMENT "完整时间",
                           bool  BOOLEAN COMMENT "bool测试"
															 ) ENGINE=OLAP UNIQUE KEY(`id`) COMMENT 'OLAP' DISTRIBUTED BY HASH(`id`) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "is_being_synced" = "false", "storage_format" = "V2", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" );
 
 insert into create_test VALUES(1,1234,5.7,'小司','小小司','2023-01-01 00:00:00',false)
insert into create_test VALUES(2,123567,5.7767,'小李','小小李','2023-02-01 00:00:00',true)
select * from create_test

#创建表2
 CREATE TABLE `create_test2` (id int COMMENT "id",
                           seri_id BIGINT COMMENT "序列id",
                           price  DECIMAL(2) COMMENT "价格",
                           price_d  Double COMMENT "同价格"
															 ) ENGINE=OLAP UNIQUE KEY(`id`) COMMENT 'OLAP' DISTRIBUTED BY HASH(`id`) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "is_being_synced" = "false", "storage_format" = "V2", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" );
 
insert into create_test2 VALUES(1,1234,5.7,5.7);
insert into create_test2 VALUES(2,123567,5.7767,5.7767);
ALTER TABLE create_test2 RENAME COLUMN `price`  'price11'

select * from create_test2

 select * from (${sql}) t 
 
DROP TABLE IF EXISTS create_test3

delete from IF EXISTS table  create_test3

alter table create_test2 drop  column  iprice_d;

select * from (select * from create_test2) t

ALTER TABLE create_test2 RENAME COLUMN price11 price;

ALTER TABLE create_test2 RENAME COLUMN price price_d;

CREATE TABLE IF NOT EXISTS `Table_test21`(`service_id`  BIGINT,`type_id`  BIGINT,`sn`  STRING,`_time`  BIGINT,`id` INT,`name` STRING)
UNIQUE KEY(service_id)
DISTRIBUTED BY HASH(service_id) BUCKETS 1
PROPERTIES("replication_num" = "1");


 
#CREATE TABLE IF NOT EXISTS `iot_serviceId`(`service_id`  BIGINT,`type_id`  BIGINT,`sn`  STRING,`_time`  BIGINT)
CREATE TABLE IF NOT EXISTS `iot_12875984u66935763`(`type_id`  BIGINT,`sn`  STRING,`_time`  BIGINT,`high`  STRING)
UNIQUE KEY(service_id)
DISTRIBUTED BY HASH(service_id) BUCKETS 1
PROPERTIES("replication_num" = "1");

select * from service_id;





insert into talg VALUES(21213,123567,'sn小李',122324356465);
insert into talg VALUES(212131,1235671,'sn小李1',1223243564651);
insert into talg VALUES(2121311,1235671,'1',1223243564651);
insert into talg(service_id,type_id,sn,_time)   VALUES(2121311312,'1235671','3',1223243564651);

INSERT INTO talg ( type_id,service_id,sn,_time ) VALUES(a1234,33333,789,1234364);

select sum(sn+0) from talg where sn > 1

select * from talg where sn = 'sn小李'

select sum(sn+0) from talg where sn > 1

delete from talg partition p1 where  sn > 1
SHOW DELETE from talg





CREATE TABLE IF NOT EXISTS `Table_test212`(`service_id`  BIGINT,`type_id`  BIGINT,`sn`  STRING,`_time`  BIGINT,`id` INT,`name` STRING)
UNIQUE KEY(service_id)
DISTRIBUTED BY HASH(service_id) BUCKETS 1
PROPERTIES("replication_num" = "1");





show create table  df_dataSet



INSERT INTO iot_1691351022229794817 ( createTime,type_id,service_id,sn,_time,deviceId,deviceName,deviceStatus ) VALUES(2023-10-31 03:17:34,1687292753244864514,1691351022229794817,gateway9528,1698985075576,99,192.168.200.247,空闲);


CREATE TABLE IF NOT EXISTS `iot_1691351022229794817`(
service_id BIGINT,
`type_id`  BIGINT,
`sn`  STRING,
`_time`  BIGINT,
createTime STRING ,
deviceId STRING,
deviceName STRING,
deviceStatus STRING
)
UNIQUE KEY(service_id)
DISTRIBUTED BY HASH(service_id) BUCKETS 1
PROPERTIES("replication_num" = "1");

INSERT INTO iot_1691351022229794817 ( createTime,type_id,service_id,sn,_time,deviceId,deviceName,deviceStatus ) 
VALUES('2023-10-31 03:17:34',1687292753244864514,1691351022229794817,'gateway9528',1698985075576,99,'192.168.200.247','空闲');