=Start=

缘由：

看到一处不错的设计，简单整理学习一下其中我还不太熟悉的一些知识点，方便后面有需要的时候可以借鉴和参考。

正文：

参考解答：

始于即席查询SQL语句的分析

从库表字段的创建开始，想着后面使用的时候怎么会更方便

step1. 建表-给字段设定类型
CREATE TABLE IF NOT EXISTS datasec.adhoc_sql_parse (
...
,user_id_info  ARRAY < STRING >  COMMENT 'user_id_info'
...
)

step2. 查询后填充-给字段填充内容
,sort_array(collect_set(case when nickname is null then null else to_json(named_struct('user_id',uid, 'nickname',nickname, 'user_type',user_type, 'is_secret',is_secret, 'status',status)) end)) as user_id_info

step3. 使用
SELECT
get_json_object(uid_info1,'$.user_id') as uid,
get_json_object(uid_info1,'$.nickname') as nickname,
get_json_object(uid_info1,'$.user_type') as user_type,
get_json_object(uid_info1,'$.is_secret') as is_secret,
get_json_object(uid_info1,'$.status') as status,
uid_info1,
user_id_info,
date
FROM
  datasec.adhoc_sql_parse
lateral view explode(user_id_info) uid_info as uid_info1
WHERE
  date BETWEEN '20241201' AND '20241201'
  and get_json_object(uid_info1,'$.user_id') not in ('10000001','10000000001')
  and (
    query_table rlike 'gmv|pay|order|amount|price|address|mobile|phone|salary|income'
  )
  and (
    (user_id_info is not NULL and size(user_id_info) >0)
  )

学习其中涉及的Hive SQL函数

named_struct 函数

创建具有指定的字段名称(Key)和值(Value)的结构(struct)。

> SELECT named_struct('a', 1, 'b', 2, 'c', 3);
 {"a":1,"b":2,"c":3}

to_json 函数

将 Hive 结构（例如：LIST，MAP，NAMED_STRUCT 等）转换成为 JSON字符串，一般会配合另一个 named_struct()函数（自定义结构化数据的KV）一起使用

##### 语法

to_json(expr [, options] )

##### 参数
* expr：STRUCT 表达式，或者 Databricks SQL 和 Databricks Runtime 15.3 及更高版本中的 VARIANT。
* options：一个可选的 MAP 文本表达式，其键和值为 STRING。 如果 expr 是 VARIANT，则忽略这些选项。

##### 返回
一个 STRING。

##### 示例

> SELECT to_json(named_struct('a', 1, 'b', 2));
 {"a":1,"b":2}

> SELECT to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
 {"time":"26/08/2015"}

> SELECT to_json(array(named_struct('a', 1, 'b', 2)));
 [{"a":1,"b":2}]

> SELECT to_json(map('a', named_struct('b', 1)));
 {"a":{"b":1}}

> SELECT to_json(map(named_struct('a', 1),named_struct('b', 2)));
 {"[1]":{"b":2}}

> SELECT to_json(map('a', 1));
 {"a":1}

> SELECT to_json(array((map('a', 1))));
 [{"a":1}]

-- VARIANT input
> SELECT to_json(parse_json('{"key": 123, "data": [4, 5, "str"]}'))
  {"data":[4,5,"str"],"key":123}

Hive中常用于json格式字符串处理的函数

1. string类型 -> json
* named_struct: 生成key和value的struct结构，传递参数为 named_struct(key1,value1,key2,value2, ..., keyN,valueN)
* to_json: 将各种复杂结构（例如：LIST，MAP，NAMED_STRUCT 等）转换成json格式

select to_json(named_struct('a', 1, 'b', 2));

2. map类型 -> json
* str_to_map 可以先将string切割成map，再转json
* to_json: 将各种复杂结构（例如：LIST，MAP，NAMED_STRUCT 等）转换成json格式

select to_json(str_to_map("aaaa_-100#bbbb_领券29减8#CCCC_29分钟#CCCC_50分钟",'#','_')) as json_test

Hive中处理json数据的两种方式

第一种：将json数据作为字符串进行处理
* get_json_object - 一次取一个字段
* json_tuple - 一次取多个字段

第二种：通过专门的解析类直接加载一个json格式的数据到Hive中

参考链接：

Azure Databricks SQL 内置函数 #nice
https://learn.microsoft.com/zh-cn/azure/databricks/sql/language-manual/sql-ref-functions-builtin

to_json 函数
https://learn.microsoft.com/zh-cn/azure/databricks/sql/language-manual/functions/to_json

Tencent-日志服务（Cloud Log Service，CLS）->统计分析（SQL）>SQL 函数
https://cloud.tencent.com/document/product/614/44063

alibabacloud-日志服务SLS是云原生观测与分析平台->SQL函数
https://www.alibabacloud.com/help/zh/sls/user-guide/sql-function/

hiveSql冷门但好用函数 –持续更新
https://blog.csdn.net/i_mycode/article/details/128107201

Hive–map/string→json&处理Json数据的函数 #nice
https://blog.csdn.net/qq_46893497/article/details/109959763

Converting data from multiple Hive Tables to Complex JSON
https://stackoverflow.com/questions/25188734/converting-data-from-multiple-hive-tables-to-complex-json

Hive and JSON made simple
https://brickhouseconfessions.wordpress.com/2014/02/07/hive-and-json-made-simple/

Hive Operators and User-Defined Functions (UDFs)
https://cwiki.apache.org/confluence/display/hive/languagemanual+udf#LanguageManualUDF-json_tuple

Hive之函数解析
https://www.cnblogs.com/OnePandas/p/17953263

=END=