【将json字符串转成多列的函数】

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH
      cte_base_i18n_lang_dict AS (SELECT explode(udf.read_json(zh_cn)) AS j_zh_cn
                                FROM stg.stg_base_i18n_lang_dict
                                WHERE dt = '2025-10-12'
                                  AND delete_flag = '0'
                                  AND code in ('enum_inventory_BillTypeInEnum', 'enum_inventory_BillTypeOutEnum',
                                               'enum_AdjustBillTypeEnum')
                                  AND type = '2'
                                  AND state = '0'),
    -- 处理json
    cte_i18 AS (SELECT get_json_object(j_zh_cn, '$.code') AS code, get_json_object(j_zh_cn, '$.desc') AS desc
                FROM cte_base_i18n_lang_dict)
SELECT * FROM cte_i18

【IDB审核平台】导入表建表

1
2
3
4
5
6
7
8
9
10
11
12
13
create table ld_mapping_fixed_price_city_cost
(
    id                           bigint unsigned not null primary key auto_increment comment '数据表主键',

    upload_batch_no              varchar(255)    not null default '' comment '上传操作-批次号',
    delete_flag                  int             not null default 0 comment '删除 默认；0:不删除,1:已删除',
    create_user                  varchar(20)     not null default '' comment '创建人',
    create_user_name             varchar(30)     not null default '' comment '创建人姓名',
    create_time                  datetime        not null default CURRENT_TIMESTAMP comment '创建时间',
    edit_user                    varchar(20)     not null default '' comment '修改人',
    edit_user_name               varchar(30)     not null default '' comment '修改人姓名',
    edit_time                    datetime        not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '修改时间'
) comment '一口价竞价城市成本参数表'
1
2
alter table ld_asset_operations_vietnam_offline_import
  add column unit varchar(400) NOT NULL DEFAULT '' COMMENT '单位' after ratio

【快捷键】

  • 修改电脑开机密码:Ctrl+Alt+Delete
  • 无痕模式；Ctrl+Shift+N
  • 标记多列：ctrl+Alt+向下箭头

【finebi基本使用】

编辑链接

预览链接 –仪表盘预览链接;http://ip:端口/webroot/decision/v5/design/report/此处放置仪表板ID/view

【数据中台】

【中台任务-shell】

1
2
ssh root@prd-za-hadoop01.hd "su hdfs -s /data/command/core_shell/hdfs_to_doris.sh ads ads_store_health_dashboard nopart all 2025-02-24 2025-02-24 fanruan fanruan_hive_ads_store_health_dashboard prd_report_doris"
ssh root@prd-za-hadoop01.hd "su hdfs -s /data/command/core_shell/hdfs_to_doris.sh rpt rpt_material_warehouse_day  dt inc $1 $2 fanruan fanruan_hive_rpt_material_warehouse_day prd_report_doris"

【中台调参数】

1
2
3
4
5
6
7
8
9
10
11
12
//任务执行效率低调高 park.num.executors、driver.memory
spark.name：rpt_collection_letter_registration
spark.executor.memory：12
spark.executor.cores：4
spark.num.executors：4
spark.sql.shuffle.partitions：32
driver.memory：2
spark.executor.memory：spark.executor.cores = 3:1
spark.sql.shuffle.partitions = spark.executor.cores * spark.num.executors * 2

//业务代码中必须用到笛卡尔集时
set spark.sql.crossJoin.enabled=true;

【中台建表】

1
2
3
4
5
6
7
8
9
10
11
12
--建表语句
create external table dwd|dws|ads|rpt_test_table (
column_name type comment '测试',
...
) COMMENT '测试'
-- 分区表 partitioned by (dt string comment '按天分区') 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001' 
STORED AS PARQUET 
TBLPROPERTIES (
'transactional' = 'false',
'parquet.compression' = 'snappy'
);

【测试表恢复】

1
2
INSERT OVERWRITE TABLE rpt_dev.rpt_business_managers_lease_change_data
        select null,null,null,null,null,null,null,null,null,null,null,null

【doris操作】

【 doris增减分区脚本】

1
2
3
4
5
6
7
8
9
10
--分区表参考：设备信息表
ALTER TABLE fanruan_hive_ads_fin_comn_store_repair_fee_da SET ("dynamic_partition.enable" = "true")
ALTER TABLE fanruan_hive_ads_fin_comn_store_repair_fee_da DROP PARTITION p20250228;
ALTER TABLE fanruan_hive_ads_fin_comn_store_repair_fee_da ADD PARTITION p20250129 VALUES [('2025-01-29'), ('2025-01-30'))

--表字段前加字段
ALTER TABLE ld_hr_solution_coef ADD COLUMN advice_soln varchar(500) not null default '' COMMENT '提点方案' AFTER price_coef_start_date

--doris添加列
alter table fanruan_hive_rpt_material_warehouse_day add column mat_property string comment '材料产权';

【经典案列】

  • 列转行：项目维度应收应付结算付款汇总表
  • 数据测试
1
2
3
4
5
6
7
8
9
10
11
12
13
select count(1) from stg.stg_fac_voucher_detail where dt = '${bizdate,yyyy-MM-dd,day,-,0}'
and delete_flag = '0'
union ALL
select count(distinct voucher_id,row_id) from stg.stg_fac_voucher_detail where dt = '${bizdate,yyyy-MM-dd,day,-,0}'
and delete_flag = '0'

select voucher_id,count(1) as tp from stg.stg_fac_voucher_header where dt = '${bizdate,yyyy-MM-dd,day,-,0}'
and delete_flag = '0'
group by voucher_id having count(1)>1
order by tp

select * from stg.stg_fac_voucher_header where dt = '${bizdate,yyyy-MM-dd,day,-,0}'
and delete_flag = '0' and voucher_id = '571525492425105408'