Doris-03-常用脚本汇总

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