ES数据处理方法

由于日志数据存在ES项目里，需要从ES中获取日志进行分析，使用SQL数据进行处理，如下：

select

     traceid--    STRING   COMMENT '流程id',

    ,appnum  --   BIGINT   COMMENT '迭代号',

    ,appversion --STRING   COMMENT 'APP版本',    

    ,appcode   -- STRING   COMMENT '应用编码',

    ,type     --  STRING   COMMENT '类型',

    ,spanid   --  STRING   COMMENT '模块id',  

    ,apptype   -- STRING   COMMENT '应用类型详情见定义',

    ,eventtime -- DATETIME COMMENT '日期',

    ,name      -- STRING   COMMENT '名称',

    ,id        -- STRING   COMMENT 'id',

    ,theid     -- STRING   COMMENT 'theId'  

    ,preid

    -------------data----------------    

    ,GET_JSON_OBJECT(data_tmp,'$.allInOne') AS allInOne  

    ,GET_JSON_OBJECT(data_tmp,'$.class')      AS class

    ,GET_JSON_OBJECT(data_tmp,'$.classCode')      AS classCode

    ,GET_JSON_OBJECT(data_tmp,'$.deviceId')     AS deviceId

    ,GET_JSON_OBJECT(data_tmp,'$.grade')   AS grade

    ,GET_JSON_OBJECT(data_tmp,'$.gradeCode') AS gradeCode

    ,GET_JSON_OBJECT(data_tmp,'$.handleTime') AS handleTime

    ,GET_JSON_OBJECT(data_tmp,'$.heigth') AS heigth

    ,cast(ipint(GET_JSON_OBJECT(json_build,'$.ip')) as string) AS ipAddr

    ,GET_JSON_OBJECT(data_tmp,'$.isSuccess') AS isSuccess     --isSuccess(1.是 0否)

    ,GET_JSON_OBJECT(data_tmp,'$.loginMode') AS loginMode     -- 登陆模式 1 游客登陆 2 账户登陆

    ,GET_JSON_OBJECT(data_tmp,'$.loginType') AS loginType     -- 登陆方式 1：在线登陆 2 离线登陆

    ,GET_JSON_OBJECT(data_tmp,'$.school') AS school

    ,GET_JSON_OBJECT(data_tmp,'$.schoolCode') AS schoolCode  

    ,GET_JSON_OBJECT(data_tmp,'$.width') AS width

    ,GET_JSON_OBJECT(data_tmp,'$.subject') AS subject

    ,GET_JSON_OBJECT(data_tmp,'$.subjectCode') AS subjectCode

    ,GET_JSON_OBJECT(data_tmp,'$.classTime') AS classTime

    ,GET_JSON_OBJECT(data_tmp,'$.reason') AS reason

    ,GET_JSON_OBJECT(data_tmp,'$.operateVersion') AS operateVersion  

    ----------新增---------

    ,CASE WHEN GET_JSON_OBJECT(data_tmp,'$.userId')  is not NULL THEN GET_JSON_OBJECT(data_tmp,'$.userId')

          WHEN GET_JSON_OBJECT(data_tmp,'$.teacherCode') is not null THEN  GET_JSON_OBJECT(data_tmp,'$.teacherCode')

          ELSE  GET_JSON_OBJECT(data_tmp,'$.userId')

     END  AS userId          

    ,GET_JSON_OBJECT(data_tmp,'$.userName') AS userName

    ,GET_JSON_OBJECT(data_tmp,'$.userType') AS userType

    ,GET_JSON_OBJECT(data_tmp,'$.account') AS account

    ,GET_JSON_OBJECT(data_tmp,'$.courseId') AS courseId  

    ,GET_JSON_OBJECT(data_tmp,'$.pageName') AS pageName  

    ,GET_JSON_OBJECT(data_tmp,'$.pageTitle') AS pageTitle  

    ,CASE    WHEN GET_JSON_OBJECT(data_tmp,'$.describe') is not NULL  THEN GET_JSON_OBJECT(data_tmp,'$.describe')

             WHEN GET_JSON_OBJECT(data_tmp,'$.eventDesc') is not NULL  THEN GET_JSON_OBJECT(data_tmp,'$.eventDesc')

             ELSE  ''

             END AS  describes    

    ,GET_JSON_OBJECT(data_tmp,'$.source') AS source      

    ,GET_JSON_OBJECT(data_tmp,'$.topDistance') AS topDistance  

    ,GET_JSON_OBJECT(data_tmp,'$.size') AS sizes  

   ---------------json_build--------------------

    ,GET_JSON_OBJECT(json_build,'$.sysVersion')   AS sysVersion    

    ,GET_JSON_OBJECT(json_build,'$.cpuType')     AS cpuType  

    ,GET_JSON_OBJECT(json_build,'$.memory') AS memory

    ,GET_JSON_OBJECT(json_build,'$.netType') AS netType

    ,GET_JSON_OBJECT(json_build,'$.sysName') AS sysName

    ,GET_JSON_OBJECT(json_build,'$.deviceModel') AS deviceModel

    ,GET_JSON_OBJECT(json_build,'$.deviceNo') AS deviceNo  

   -------------------新增--------------------

   ,GET_JSON_OBJECT(json_build,'$.screenHeight') AS screenHeight  

   ,GET_JSON_OBJECT(json_build,'$.screenWidth') AS screenWidth  

   ,GET_JSON_OBJECT(json_build,'$.browserName') AS browserName

   ,GET_JSON_OBJECT(json_build,'$.browserVersion') AS browserVersion

   ,GET_JSON_OBJECT(json_build,'$.browserWidth') AS browserWidth

   ,GET_JSON_OBJECT(json_build,'$.browserHeight') AS browserHeight

   ,GET_JSON_OBJECT(json_build,'$.ip') AS ip

   ,GET_JSON_OBJECT(json_build,'$.remoteIp') AS remoteIp

   ,GET_JSON_OBJECT(data_tmp,'$.actionName') AS actionName

   ,GET_JSON_OBJECT(data_tmp,'$.finishStatus') AS finishStatus

   ,GET_JSON_OBJECT(data_tmp,'$.isFirst') AS isFirst

   ,GET_JSON_OBJECT(data_tmp,'$.bankType') AS bankType

   ,GET_JSON_OBJECT(data_tmp,'$.book') AS book

   ,GET_JSON_OBJECT(data_tmp,'$.mode') AS mode

   ,GET_JSON_OBJECT(data_tmp,'$.chapter') AS chapter

   ,GET_JSON_OBJECT(data_tmp,'$.result') AS result

   ,GET_JSON_OBJECT(data_tmp,'$.knowledgeCount') AS knowledgeCount

   ,GET_JSON_OBJECT(data_tmp,'$.questCount') AS questCount

   ,GET_JSON_OBJECT(data_tmp,'$.scoreType') AS scoreType

   ,GET_JSON_OBJECT(data_tmp,'$.scoreModule') AS scoreModule

   ,GET_JSON_OBJECT(data_tmp,'$.appName') AS appName

   ,GET_JSON_OBJECT(data_tmp,'$.voteNumber') AS voteNumber

   ,GET_JSON_OBJECT(data_tmp,'$.perVoteNubmer') AS perVoteNubmer

   ,GET_JSON_OBJECT(data_tmp,'$.type') AS attributeType

--- 新增 2022-12-09 ----

   ,GET_JSON_OBJECT(data_tmp,'$.loginTypeName') AS loginTypeName

   ,GET_JSON_OBJECT(data_tmp,'$.name') AS noteName

   ,GET_JSON_OBJECT(data_tmp,'$.notes') AS notes

   ,GET_JSON_OBJECT(data_tmp,'$.pageNum') AS pageNum

   ,GET_JSON_OBJECT(data_tmp,'$.color') AS color  

   ,GET_JSON_OBJECT(data_tmp,'$.event') AS event

   ,GET_JSON_OBJECT(data_tmp,'$.date') AS  switchDate

   ,GET_JSON_OBJECT(data_tmp,'$.input') AS  inputValue  

   ,GET_JSON_OBJECT(data_tmp,'$.title') AS  title

   ,GET_JSON_OBJECT(data_tmp,'$.fileName') AS  fileName   --文件名

-- 1.文档：doc、docx、PDF

-- 2.音频：WAV、ape、AIFF、CD、AU、MP3、WMA、VQF、FLAC、MIDI、Ogg、U-Law、VOC、aac、RA/.RM/.RAM

-- 3.视频：avi、MOV/.QT、MKV、MP4、WMV、MPEG、BD、HDVD、RMVB、PROPER、R5、Watermarks、TS、DAT、SWF、ASF、3GP、FLV、HDRIP、IMAX

-- 4.课件：ppt、pptx、pps、ppsx、ppa、ppam、pot、potx、thmx

-- 5.图片：Webp、BMP、PCX、TIF、GIF、JPEG、TGA、EXIF、FPX、SVG、PSD、CDR、PCD、DXF、UFO、EPS、AI、PNG、HDRI、RAW、WMF、FLIC、EMF、ICO

-- 6.表格：xls、csv、CSS、XPS、xlsm、et、

-- 7.压缩包：RAR、ZIP、ARJ、Z、LZH、JAR

-- 8.其他

   ,GET_JSON_OBJECT(data_tmp,'$.fileId') AS  fileId      

   ,GET_JSON_OBJECT(data_tmp,'$.fileNames') AS  fileNames  

   ,GET_JSON_OBJECT(data_tmp,'$.beginDate') AS  beginDate    

   ,GET_JSON_OBJECT(data_tmp,'$.endDate') AS  endDate  

   ,GET_JSON_OBJECT(data_tmp,'$.questionId') AS  questionId   --题号

   ,GET_JSON_OBJECT(data_tmp,'$.packageName') AS  packageName  

   ,GET_JSON_OBJECT(data_tmp,'$.versionName') AS  versionName    

   ,GET_JSON_OBJECT(data_tmp,'$.versionCode') AS  versionCode

   ,GET_JSON_OBJECT(data_tmp,'$.jobId') AS  jobId

   ,GET_JSON_OBJECT(data_tmp,'$.answer') AS  answer

   ,GET_JSON_OBJECT(data_tmp,'$.wrong') AS  wrong    

   ,GET_JSON_OBJECT(data_tmp,'$.correct') AS  correct

   ,GET_JSON_OBJECT(data_tmp,'$.unanswered') AS  unanswered  

   ,GET_JSON_OBJECT(data_tmp,'$.finishNumber') AS  finishNumber  

   ,GET_JSON_OBJECT(data_tmp,'$.totalNumber') AS  totalNumber  

   ,GET_JSON_OBJECT(data_tmp,'$.word') AS  word  

   ,GET_JSON_OBJECT(data_tmp,'$.msg') AS  msg  

   ,GET_JSON_OBJECT(data_tmp,'$.count') AS  impCount  

   ,GET_JSON_OBJECT(json_build,'$.pageHeight') AS pageHeight   --页面高度

   ,GET_JSON_OBJECT(data_tmp,'$.answers') AS  answers --答题情况

   --新增--

   ,GET_JSON_OBJECT(data_tmp,'$.num') AS  num --题目数量

   ,GET_JSON_OBJECT(data_tmp,'$.op') AS  op --随机选人 选项 op(清除(NULL)、A、B、C) op(不随机(0)、1、2、3)

   ,GET_JSON_OBJECT(data_tmp,'$.leaveTime') AS  leaveTime --收卷倒计时

   ,GET_JSON_OBJECT(data_tmp,'$.examId') AS  examId --考试id

   ,GET_JSON_OBJECT(data_tmp,'$.id') AS  idCode --id，用英文逗号隔开，组code

   ,GET_JSON_OBJECT(data_tmp,'$.code') AS  code --对调学生

   ,GET_JSON_OBJECT(data_tmp,'$.rol') AS  rol --对调学生 位置rol

   ,GET_JSON_OBJECT(data_tmp,'$.col') AS  col --对调学生 位置col

   ,GET_JSON_OBJECT(data_tmp,'$.stage') AS  stage --学段

   ,GET_JSON_OBJECT(data_tmp,'$.version') AS  versions --学段

--    ,GET_JSON_OBJECT(data_tmp,'$.type') AS  见 attributeType --类型 type(1.批注作答 2.画板作答)

  -- ,GET_JSON_OBJECT(data_tmp,'$.actionName') AS actionName      --活动名称

  -- ,GET_JSON_OBJECT(data_tmp,'$.answer') AS  answer    --答案

   --,GET_JSON_OBJECT(data_tmp,'$.color') AS  color --颜色

  -- ,GET_JSON_OBJECT(data_tmp,'$.finishNumber') AS  finishNumber --找到的数量

   --,GET_JSON_OBJECT(data_tmp,'$.totalNumber') AS  totalNumber --总词数

  -- ,GET_JSON_OBJECT(data_tmp,'$.word') AS  word --未答数

  -- `completionStatus` varchar(100) DEFAULT NULL COMMENT '完成情况',

 -- ,GET_JSON_OBJECT(data_tmp,'$.event') AS  event --收起/展开事件

  --,GET_JSON_OBJECT(data_tmp,'$.fileId') AS  fileId --云端文件ID

  --,GET_JSON_OBJECT(data_tmp,'$.fileNames') AS  fileNames --文件名列表

  --,GET_JSON_OBJECT(data_tmp,'$.fileName') AS  fileName --文件名

  --,GET_JSON_OBJECT(data_tmp,'$.date') AS    dates --日期筛选类型

  --,GET_JSON_OBJECT(data_tmp,'$.result') AS  isfinish --完成情况 result：true/false

 -- ,GET_JSON_OBJECT(data_tmp,'$.input') AS  inputValue --输入值

  --,GET_JSON_OBJECT(data_tmp,'$.jobId') AS  jobId --作业ID

  --,GET_JSON_OBJECT(data_tmp,'$.name') AS  name --名称

  --,GET_JSON_OBJECT(data_tmp,'$.questionId') AS  questionId --题目ID

  --`jobType` varchar(50) DEFAULT NULL COMMENT '作业类型',

  --`noteName` varchar(50) DEFAULT NULL COMMENT '笔记本名称',

  --`signName` varchar(100) DEFAULT NULL COMMENT '标签名',  

  --`switchDate` datetime DEFAULT NULL COMMENT '日期切换日期值',

  --`thickNess` varchar(10) DEFAULT NULL COMMENT '粗细值',

  --`timeSlot` varchar(10) DEFAULT NULL COMMENT '时间段',

  --`toolName` varchar(50) DEFAULT NULL COMMENT '工具名称',

  --`wrongBookName` varchar(50) DEFAULT NULL COMMENT '错题本名称',

--  ,GET_JSON_OBJECT(json_build,'$.loginTypeName') AS appcode

--    ,GET_JSON_OBJECT(json_build,'$.appVersion') AS appVersion

,createtime  --创建时间

from (

select  theid,

                    id,

                    name,

                    eventtime,

                    apptype,

                     regexp_replace(regexp_replace(regexp_replace(build,'^\\[',''),'\\]$',''),'},\\{','}|{') AS json_build,

                    spanid,

                    type,

                    appcode,

                    regexp_replace(regexp_replace(regexp_replace(data,'^\\[',''),'\\]$',''),'},\\{','}|{') AS json_data,

                    appversion,

                    appnum,

                    preid,

                    traceid,

                    createtime

                     from  dw_es_action_log_inc_new

                    WHERE

                   --  DATETRUNC(eventtime,'hh') >=  DATETRUNC(dateadd(TO_DATE('${cyctime}','yyyymmddhhmiss'), -1, 'hh'),'hh')     or    

                   

                     DATETRUNC(createtime,'DD') >=  DATETRUNC(TO_DATE('${bizdate}','yyyymmdd'),'DD')  --测试使用            

) a0

lateral view explode(split(json_data,'\\|')) b AS data_tmp;

从中可以发现，针对很多不同格式的 数据，可以进行这种分解处理。

select bh,bjmc,nj,xxbm,xxmc,xnid,xn,xnmc,xd,rnk from (SELECT bh,bjmc,nj,xxbm,xxmc,xnid,xn,xnmc,xd, Row_Number() OVER (partition by bh,xxbm ORDER BY nj desc) rnk

FROM dw_class where  zt='1'  and  bjlxm = '1' and xnid <> '' ) aa where rnk='1'  

同时可以使用Row_Number，进行数据处理，获取最大年级数据。