Doris-常用脚本
前言
这里是记录经常使用的一些脚本
1.建表模板
--*******************************************************************
-- 业务主题:
-- 创 建 人:
-- 创建日期: 2022-04-22
-- 脚本用途:
-- 执行引擎: doris
-- 执行周期: 天
-- 目 标 表: dws.
-- 变更记录:
-- 变更日期 变更人 变更描述
--*******************************************************************
CREATE TABLE dbname.tablename(
ts date comment '{\"type\"=\"0\",\"comment\"=\"分区字段\"}'
,pick_code varchar(50) comment '{\"type\"=\"0\",\"comment\"=\"xxid\"}'
PARTITION p20220429 VALUES [('2022-04-29'), ('2022-04-30')))
DISTRIBUTED BY HASH(`pick_store_code`) BUCKETS 2
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "UTC",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "2",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);
2.调度脚本设置
set time_zone='Asia/Shanghai';
set query_timeout=1000;
SET exec_mem_limit=20000000000;
3.行转列
,group_concat(if(status=10,operate_time ,'') ,'') as wait_pay_time
,group_concat(if(status=20,operate_time,'') ,'') as paying_time
,group_concat(if(status=30,operate_time,'') ,'') as failed_pay_time
,group_concat(if(status=40,operate_time,'') ,'') as success_pay_time
4.加载HDFS数据
LOAD LABEL 任务名称
(
DATA INFILE("hdfs://文件路径/ts=2022-05-23/*")
INTO TABLE 表名
format as "orc"
(
`field1`,`field2`,`field3`
)
COLUMNS FROM PATH AS (`ts`)
SET(ts=ts)
)
WITH BROKER hdfs_broker
(
"hadoop.security.authentication" = "kerberos",
"kerberos_principal" = "",
"kerberos_keytab" = "",
"dfs.nameservices" = "",
"dfs.ha.namenodes.cnprod1ha" = "nn1,nn2",
"dfs.namenode.rpc-address.cnprod1ha.nn1" = "",
"dfs.namenode.rpc-address.cnprod1ha.nn2" = "",
"dfs.client.failover.proxy.provider.cnprod1ha" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
)
PROPERTIES
(
"timezone" = "UTC"
)
查看任务进度
show load where label='任务名称'
5.获取数据字典并导出Excel
MAVEN配制
<dependencies>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
</dependencies>
MAIN
package com.example.ds.controller;
import com.alibaba.excel.EasyExcel;
import com.example.ds.bean.DSBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Component
public class DSCtroller implements CommandLineRunner {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void run(String... args) throws Exception {
ArrayList<DSBean> dsBeans = new ArrayList<>();
List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from information_schema.`COLUMNS` ");
String flag = "";
dsBeans.add(new DSBean("数据库名","表名", "字段名",
"字段类型", "注释"));
for (Map<String, Object> map : maps) {
String TABLE_SCHEMA = map.get("TABLE_SCHEMA").toString();
String table_name = map.get("table_name").toString();
String column_name = map.get("column_name").toString();
String data_type = map.get("data_type").toString();
String COLUMN_COMMENT = map.get("COLUMN_COMMENT").toString();
if (flag == TABLE_SCHEMA || flag.equals(TABLE_SCHEMA)){
dsBeans.add(new DSBean("",table_name, column_name, data_type, COLUMN_COMMENT));
}else{
flag = TABLE_SCHEMA;
dsBeans.add(new DSBean(TABLE_SCHEMA,"", "",
"", ""));
flag = TABLE_SCHEMA;
dsBeans.add(new DSBean("",table_name, column_name, data_type, COLUMN_COMMENT));
}
}
exportExcel(dsBeans);
System.out.println("============== 导出成功 =============");
}
public void exportExcel( ArrayList<DSBean> dsBeans ) {
try{
String fileName = "C:\\Users\\Tian\\Desktop\\数据字典" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去读,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName).sheet("数据字典").doWrite(dsBeans);
}catch (Exception e){
e.printStackTrace();
}
}
}
BEAN
public class DSBean {
@ExcelProperty(value = "表名称")
private String tableName;
@ExcelProperty(value = "库名称")
private String tableSchema;
@ExcelProperty(value = "字段名称")
private String columnName;
@ExcelProperty(value = "字段类型")
private String dataType;
@ExcelProperty(value = "注释")
private String ColumnComment;
application.yml配制
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver