常用_创建udf函数

节点：131

hadoop fs -mkdir /data/apidata/udf
hadoop fs -put /opt/fubo/xx.jar /data/apidata/udf

add jars hdfs://data/apidata/udf/untitled-1.0-SNAPSHOT.jar

create function qdmedev.jsonArray as "org.example.GetJsonArray";

hive
show databases;
use qdemedev;
DROP FUNCTION IF EXISTS jsontoarray;
CREATE FUNCTION jsontoarray AS 'org.example.GetJsonArray'
USING JAR 'hdfs:///data/apidata/udf/untitled-1.0-SNAPSHOT20231006.jar';

编写java程序

1. 使用maven创建java工程
2. 编写pom文件，再官网上jar包版本
3. 理清思路，拆解问题
4. 编写每个步骤的demo
5. 组合脚本

编写java其中的工具类

1. 考虑输入什么，输出什么

编写好每个步骤的打印，方便知道是哪一步卡住

spark提交
spark-submit --class TestClass
--master yarn
--queue ${指定队列名称}
--deploy-mode client
--driver-memory 1G
--conf spark.driver.maxResultSize=1G
--driver-cores 2
--num-executors 4
--executor-cores 4
--executor-memory 16G
--conf spark.dynamicAllocation.enabled=true
--conf spark.shuffle.service.enabled=true
--conf spark.sql.shuffle.partitions=6400
--conf spark.default.parallelism=6400
--conf spark.storage.memoryfraction=0.4
--conf spark.shuffle.memoryFraction=0.4
--conf spark.blacklist.enabled=true
--conf spark.speculation=true
--conf spark.hadoop.hive.exec.orc.split.strategy=ETL
--name scala_test
AtestSparkApplication.jar

spark-submit --class org.example.App3
--master yarn
--deploy-mode client
--driver-cores 10
--driver-memory 10G \ ## 实测运行服务器，运行内存（java 进程需要内存）
--executor-memory 20G
--conf spark.dynamicAllocation.enabled=true
--conf spark.shuffle.service.enabled=true
--conf spark.sql.shuffle.partitions=6400
--conf spark.default.parallelism=6400
--conf spark.storage.memoryfraction=0.4
--conf spark.shuffle.memoryFraction=0.4
./untitled-1.0-SNAPSHOT2023101001.jar "select * from dmr.dmr_qs_qccth_ccb_dtl ;" "PROJECT_MIS" "/data/apidata/data/testfubo/" "qdmedev.ods_api_dmr_qs_qccth_ccb_dtl"

sh postDDMjson.sh "select row_id ,period_wid ,organization_wid ,product_wid ,model_year ,n_mis ,vin ,product_date ,product_month_int ,buy_date ,buy_date_int ,vehicle_type ,brand ,brand_son ,xifen_mak ,series_code ,series_name ,model_code ,model_name ,product_place ,power_type ,power_brand ,power_type_dalei ,power_series ,displacement_l ,engine_name ,engine_producter ,engine_product_place ,gearbox_type ,invoice_object ,car_charactor ,sales_address ,claim_no ,claim_ccc ,claim_date ,claim_date_int ,approve_date ,is_active ,fix_type ,problem_mile ,m_vrt_code ,m_vrt ,m_vfg_code ,m_vfg ,m_ccc_code ,m_ccc ,balance_amount ,fix_bill ,total_bill ,work_code ,work_name ,old_item_code ,old_item_name ,source_code ,mis3_r_goal_zpp ,mis12_r_goal_zpp ,mis36_r_goal_zpp ,mis3_c_goal_zpp ,mis12_c_goal_zpp ,mis36_c_goal_zpp ,mis3_r_goal_cp ,mis12_r_goal_cp ,mis36_r_goal_cp ,mis3_c_goal_cp ,mis12_c_goal_cp ,mis36_c_goal_cp ,mis3_r_goal_zpp_f ,mis12_r_goal_zpp_f ,mis36_r_goal_zpp_f ,mis3_c_goal_zpp_f ,mis12_c_goal_zpp_f ,mis36_c_goal_zpp_f ,mis3_r_goal_cp_f ,mis12_r_goal_cp_f ,mis36_r_goal_cp_f ,mis3_c_goal_cp_f ,mis12_c_goal_cp_f ,mis36_c_goal_cp_f ,mis3_r_goal_vrt ,mis12_r_goal_vrt ,mis36_r_goal_vrt ,mis3_c_goal_vrt ,mis12_c_goal_vrt ,mis36_c_goal_vrt ,mis3_r_goal_vfg ,mis12_r_goal_vfg ,mis36_r_goal_vfg ,mis3_c_goal_vfg ,mis12_c_goal_vfg ,mis36_c_goal_vfg ,mis3_r_goal_vrt_f ,mis12_r_goal_vrt_f ,mis36_r_goal_vrt_f ,mis3_c_goal_vrt_f ,mis12_c_goal_vrt_f ,mis36_c_goal_vrt_f ,mis3_r_goal_vfg_f ,mis12_r_goal_vfg_f ,mis36_r_goal_vfg_f ,mis3_c_goal_vfg_f ,mis12_c_goal_vfg_f ,mis36_c_goal_vfg_f ,mis3_r_goal_jixi ,mis12_r_goal_jixi ,mis36_r_goal_jixi ,mis3_c_goal_jixi ,mis12_c_goal_jixi ,mis36_c_goal_jixi ,mis3_r_goal_jixi_f ,mis12_r_goal_jixi_f ,mis36_r_goal_jixi_f ,mis3_c_goal_jixi_f ,mis12_c_goal_jixi_f ,mis36_c_goal_jixi_f ,integration_id ,aux_changed_on_dt ,w_insert_dt ,w_update_dt ,series_cb_name ,is_group ,group_type ,dis_ch ,repair_type from dmr.DMR_QS_R1000_CUP_F limit 10 ;" "PROJECT_MIS" "/data/apidata/data/testfubo/" "qdmedev.ods_api_dmr_qs_r1000_cup_f"

错误： Invalid signature file digest for Manifest main attributes
解决：
zip -d ./untitled-1.0-SNAPSHOT2023101001.jar META-INF/.RSA META-INF/.DSA META-INF/*.SF

查询机加上线点
SELECT F_LINECODE,TO_CHAR(f_time,'yyyy-mm-dd') ,COUNT(1) FROM MES_STATIONDATA_MACHINE
WHERE F_LINECODE IN ('MA01','MA02','MA03','MA04','MA05','MA06','MA07','MA08','MA09','MA10','J40','J41') --
AND F_STATIONNAME = 'OP0020'
AND F_TIME > TO_DATE('2022-01-01','yyyy-mm-dd')
GROUP BY F_LINECODE,TO_CHAR(f_time,'yyyy-mm-dd')
ORDER BY F_LINECODE,TO_CHAR(f_time,'yyyy-mm-dd') DESC ;

toString(); java.lang.NullPointerException的处理；
String.valueOf(mutex)

解析
select t.results
,t1.results1
from
(select ddm_result
from qdmedev.ods_api_dmr_qs_qccth_ccb_dtl_ls where ds = '2023-09-25') a
lateral view json_tuple(a.ddm_result,'results') t as results
lateral view explode(jsonarray(t.results)) t1 as results1;

select t.results
from
(select ddm_result
from qdmedev.ods_api_dmr_qs_qccth_ccb_dtl_ls where ds = '2023-09-25') a
lateral view json_tuple(a.ddm_result,'results') t as results
lateral view explode(jsonarray(t.results)) t1 as results1;

pom参考

4.0.0

org.example
untitled
1.0-SNAPSHOT



org.apache.maven.plugins
maven-compiler-plugin

7
7

org.apache.maven.plugins
maven-shade-plugin
2.4.3


package

shade




main_api

jar

untitled
http://maven.apache.org

UTF-8 2.3.2 2.6.0 3.1.0 junit junit 3.8.1 test

org.apache.hadoop
hadoop-common
3.1.1
provided


org.apache.hive
hive-exec
3.1.0
provided

com.alibaba
fastjson
1.2.83

org.apache.spark
spark-core_2.11
${spark.version}
provided

org.apache.spark
spark-sql_2.11
${spark.version}
provided


org.apache.spark
spark-hive_2.12
${hive.version}
provided

org.apache.spark
spark-streaming_2.11
${spark.version}
provided

------shell 日期增加、遍历-------
shell 日期增加
date -d "2020-01-01 3 month" "+%F"

startdate=date -d "$2" +%Y-%m-%d
enddate=date -d "$3" +%Y-%m-%d

tablequerySql="select * from dmr.DMR_QS_R1000_CUP_F "
sql="$tablequerySql where $where < '$begindate'"

echo "sql: $sql"

sh /opt/fubo/postDDMjson.sh "$sql" "PROJECT_MIS" "/data/apidata/data/testfubo/" "qdmedev.ods_api_dmr_qs_r1000_cup_f"

while [[ $startdate < $enddate ]]
do
sql="$tablequerySql where $where >= '$startdate' and $where < 'date -d "+3 month $startdate" +%Y-%m-%d'"
echo "sql:$sql"

sh /opt/fubo/postDDMjson.sh "$sql" "PROJECT_MIS" "/data/apidata/data/testfubo/" "qdmedev.ods_api_dmr_qs_r1000_cup_f"

startdate=date -d "+3 month $startdate" +%Y-%m-%d

done

------shell 日期增加-------

------DDM请求脚本-------
sh DDM01.sh buy_date 2006-01-01 2023-10-09;

sh DDMdmr_qs_twr_tws_f.sh buy_date 2011-01-01 2023-10-10;

spark-submit --class org.example.App3
--master yarn
--deploy-mode client
--driver-cores 10
--driver-memory 10G
--executor-memory 20G
--conf spark.dynamicAllocation.enabled=true
--conf spark.shuffle.service.enabled=true
--conf spark.sql.shuffle.partitions=6400
--conf spark.default.parallelism=6400
--conf spark.storage.memoryfraction=0.4
--conf spark.shuffle.memoryFraction=0.4
./untitled-1.0-SNAPSHOT2023101001.jar "select * from dmr.dmr_qs_tgw_cs_f ;" "PROJECT_MIS" "/data/apidata/data/testfubo/" "qdmedev.ods_api_dmr_qs_tgw_cs_f"

spark-submit --master yarn
--deploy-mode client
--driver-cores 10
--driver-memory 10G
--executor-memory 20G
--conf spark.dynamicAllocation.enabled=true
--conf spark.shuffle.service.enabled=true
--conf spark.sql.shuffle.partitions=6400
--conf spark.default.parallelism=6400
--conf spark.storage.memoryfraction=0.4
--conf spark.shuffle.memoryFraction=0.4
--class org.example.App3
./untitled-1.0-SNAPSHOT2023101001.jar "select * from dmr.dmr_qs_qccth_ccb_dtl ;" "PROJECT_MIS" "/data/apidata/data/testfubo/" "qdmedev.ods_api_dmr_qs_qccth_ccb_dtl"

------DDM请求脚本-------

-----spark远程调试-------
spark-submit
--master yarn
--deploy-mode client
--num-executors 2
--executor-memory 3G
--executor-cores 3
--driver-memory 5G
--conf spark.default.parallelism=100
--conf spark.storage.memoryFraction=0.4
--conf spark.shuffle.memoryFraction=0.3
--driver-java-options "-Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=y,address=5005"
--class org.example.tohiveTest
/opt/fubo/untitled-1.0-SNAPSHOT2023101001.jar "select * from dmr.DMR_QS_R1000_CUP_F limit 2" "PROJECT_MIS" "/data" "qdmedev.testfubo"

------hive调优------
Maper:
mapreduce.map.java.opts=-Xmx20480m(默认参数，表示jvm堆内存,注意是mapreduce不是mapred)
mapreduce.map.memory.mb=23040(container的内存）

Reducer:
mapreduce.reduce.java.opts=-=-Xmx20480m(默认参数，表示jvm堆内存)
mapreduce.reduce.memory.mb=23040(container的内存)
set mapreduce.map.java.opts="-Xmx20480m";
set mapreduce.map.memory.mb=23040;
set mapreduce.reduce.java.opts="-Xmx20480m";
set mapreduce.reduce.memory.mb=23040;

------spark参数----------
./bin/spark-submit
--master yarn-cluster
--num-executors 100
--executor-memory 6G
--executor-cores 4
--driver-memory 1G
--conf spark.default.parallelism=1000
--conf spark.storage.memoryFraction=0.5
--conf spark.shuffle.memoryFraction=0.3

-----------生产spark-submit命令--------------
spark-submit --master yarn
--deploy-mode cluster
--num-executors 2
--executor-memory 10G
--executor-cores 5
--executor-memory 20G
--conf spark.default.parallelism=1000
--conf spark.storage.memoryfraction=0.4
--conf spark.shuffle.memoryFraction=0.4
--class org.example.App3
/opt/fubo/untitled-1.0-SNAPSHOT20231018.jar "$querysql" "$queryproject" "$tempSavepath" "$targetTable"
[deploy@shuqi3 fubo]$ sh postDDMjson.sh "select * from dim.DIM_HR_ORGANIZATION_D" "PROJECT_HR" "/data/apidata/data/testfubo/" "qdmeprod.ods_api_dim_hr_organization_d"

sh DDM02.sh "PERIOD_WID" "201512" "202310" 5
sh postDDMjson.sh "select * from dmr.DMR_QS_R1000_CUP_F limit 2;" "PROJECT_MIS" "/data/apidata/data/testfubo/" "qdemedev.testfubo"

--------pycharm 使用anaconda3环境---------

1. 安装anaconda3,命令加入path, conda --version; conda list; conda env list;
2. 在项目得解释器中，选择已经存在得解释器，找到base环境目录下得python.exe作为解释器
3. 如果需要干净得环境，则自己建立conda env，并在里面pip install 所需库
   --------pycharm 使用anaconda3环境---------