sub_type
|
[{“A”:“1”,“B”:“2”,“C”:3},{“A”:“4”,“B”:“5”,“C”:6}]
|
通过split函数可返回如下表,即按逗号分隔开后每部分存在引号中
select split(sub_type,"},") as sub_type from appeal
需注意,返回结果的第一项末尾没有右侧的 }, 部分
sub_type |
---|
["[{“A”:“1”,“B”:“2”,“C”:3"," {“A”:“4”,“B”:“5”,“C”:6}]"] |
2.lateral view explode函数
用途:将一行数据拆分为多行数据
使用方法:lateral view explode(字段)
eg:原数据格式如下表1,之后其他函数的原数据格式同样都为表1
num | sub_type |
---|
1 | [{“A”:“1”,“B”:“2”,“C”:3},{“A”:“4”,“B”:“5”,“C”:6}] |
2 | [{“A”:“1”,“B”:“2”,“C”:3},{“A”:“4”,“B”:“5”,“C”:6}] |
3 | [{“A”:“1”,“B”:“2”,“C”:3},{“A”:“4”,“B”:“5”,“C”:6}] |
通过lateral view explode可返回如下表,即第一列的元素会与第二列同行中的每个元素相对应
select num, single_sub_type from
(select num, sub_type from t) t1 lateral view explode(split(sub_type,"},") a as single_sub_type
num | single_sub_type |
---|
1 | [{“A”:“1”,“B”:“2”,“C”:3 |
1 | {“A”:“4”,“B”:“5”,“C”:6}] |
2 | [{“A”:“1”,“B”:“2”,“C”:3 |
2 | {“A”:“4”,“B”:“5”,“C”:6}] |
3 | [{“A”:“1”,“B”:“2”,“C”:3 |
3 | {“A”:“4”,“B”:“5”,“C”:6}] |
3.regexp_replace函数
用途:替换指定字符串,相当于sql中的replace函数
使用方法:regexp_replace(字段,需替换的字符串,替换后的字符串)
通过regrexp_replace可以将字段中的[和]用空格来代替
select num, regrexp_replace(regexp_replace(single_sub_type, '\\[',''), '\\]', '') sing_sub_type,
(select num, sub_type from t) t1 lateral view explode(split(sub_type,"},") a as single_sub_type
返回结果如下
num | sing_sub_type |
---|
1 | {“A”:“1”,“B”:“2”,“C”:3 |
1 | {“A”:“4”,“B”:“5”,“C”:6} |
2 | {“A”:“1”,“B”:“2”,“C”:3 |
2 | {“A”:“4”,“B”:“5”,“C”:6} |
3 | {“A”:“1”,“B”:“2”,“C”:3 |
3 | {“A”:“4”,“B”:“5”,“C”:6} |
3.json_tuple函数&get_json_object函数
用途:json_tuple,get_json_object函数有异曲同工之妙,都是用来取json格式中的某些字段。
json指的是类似于如下格式的字段,{"firstName": "Brett", "lastName":"McLaughlin", "email": "aaaa"}
使用方法:
get_json_object(json字段, '$.其中一个个key)
json_tuple(json字段, key1, key2)
通过get_json_object或者json_tuple可返回如下表,可取出json中的每个字段.
首先需要将上一步右侧的括号}补齐,输出结果如下
select num,
case when substr(sing_sub_type, length(sing_sub_type)-1,1) = '\\}' then sing_sub_type else concat(sing_sub_type, '}') end as sin_sub_type
(select num, regrexp_replace(regexp_replace(single_sub_type, '\\[',''), '\\]', '') sing_sub_type,
(select num, sub_type from t) t1 lateral view explode(split(sub_type,"},") a as single_sub_type)
num | sin_sub_type |
---|
1 | {“A”:“1”,“B”:“2”,“C”:3} |
1 | {“A”:“4”,“B”:“5”,“C”:6} |
2 | {“A”:“1”,“B”:“2”,“C”:3} |
2 | {“A”:“4”,“B”:“5”,“C”:6} |
3 | {“A”:“1”,“B”:“2”,“C”:3} |
3 | {“A”:“4”,“B”:“5”,“C”:6} |
再取上面json中对应的字段, get_json_object用法
select num, get_json_object(sin_sub_type,$.A), get_json_object(sin_sub_type,$.B)
(select num,
case when substr(sing_sub_type, length(sing_sub_type)-1,1) = '\\}' then sing_sub_type else concat(sing_sub_type, '}') end as sin_sub_type
(select num, regrexp_replace(regexp_replace(single_sub_type, '\\[',''), '\\]', '') sing_sub_type,
(select num, sub_type from t) t1 lateral view explode(split(sub_type,"},") a as single_sub_type))
get_tuple用法
select num, A, B
(select num,
case when substr(sing_sub_type, length(sing_sub_type)-1,1) = '\\}' then sing_sub_type else concat(sing_sub_type, '}') end as sin_sub_type
(select num, regrexp_replace(regexp_replace(single_sub_type, '\\[',''), '\\]', '') sing_sub_type,
(select num, sub_type from t) t1 lateral view explode(split(sub_type,"},") a as single_sub_type))
lateral_view json_tuple(sin_sub_type, 'A', 'B')
两种方法返回结果相同
自此,字段已拆分分。总结步骤为
1.lateral view搭配split函数初步拆分
2.regrex_replace去掉多余字符
3.substr补充字段为json形式
4.json_tuple或者get_json_object获取相应字符串
参考文章:
https://blog.csdn.net/yuanyangsdo/article/details/61192275
https://blog.csdn.net/guodong2k/article/details/79459282
nvl函数LATERAL VIEW explode 必须上一个表完成之后重新select 重命名必须用asLATERAL VIEW json_tuple 必须是}不能是[regexp_replace
手动指定选项
Spark SQL的DataFrame接口支持多种数据源的操作。一个DataFrame可以进行RDDs方式的操作,也可以被注册为临时表。把DataFrame注册为临时表之后,就可以对该DataFrame执行SQL查询。
Spark SQL的默认数据源为Parquet格式。数据源为Parquet文件时,Spark SQL可以方便的执行所有的操作。
修改配置项spark.sql.sources.default,可修改默认数据源格式。
scala> val df = spark.read.load(hdfs://hadoop001:9000/nam
---------------------------可以拆分成多少个---------------
create FUNCTION [dbo].[Get_StrArrayLength]
( @str varchar(5000) , --要分割的字符串
@split varchar(10) --分隔符号
RETURNS int
BEGIN
DECLARE @location INT
DECLARE @start INT
DECLARE @le...
根据福雷斯特研究公司(ForresterResearch)的观点,SQL将成为Hadoop生态系统中最多产的应用方案之一。ApacheDrill是一个应用于大数据搜索的开源SQL查询引擎。REST服务和客户端已经成为互联网流行的技术。ApacheHBase则是一个广受欢迎的HadoopNoSQL数据库。在本文中,我将结合SQL、Hadoop、Drill、RESTwithJSON、NoSQL及HBase等技术,讨论并展示如何使用DrillRESTAPI来查询HBase和Hive。同时我也会分享一个使用DrillRESTAPI的简单jQuery客户端,利用JSON做数据交换,提供给用户一个基本的操
当多个 distinct 操作同时出现在 select 中,数据会分发多次。容易造成Reduce数据倾斜
1、如果不要求精确值,可以使用 spark-sql approx_count_distinct函数 (基数计数 hyperloglog)
2、修改SQL
基础数据准备如下, 需要计算 不同渠道下的 不同周期 的访问uv
presto:bi> desc tmp.multi_distinct_test;
Column | Type | Extra | Comment
---------+---------+-------+----------
user_id | bi
Quicksql是SQL查询产品,可用于特定的数据存储查询或多个数据存储相关查询。 它支持关系数据库,非关系数据库,甚至不支持SQL的数据存储(例如Elasticsearch,Druid)。 另外,SQL查询可以联接或合并Quicksql中多个数据存储中的数据。 例如,您可以在一种情况下执行统一SQL查询,其中一部分数据存储在Elasticsearch上,而另一部分数据存储在Hive上。 最重要的是,QSQL不依赖于任何中间计算引擎,用户只需要关注数据和统一SQL语法即可完成统计和分析。
架构图可帮助您更轻松地访问Quicksql。
QSQL体系结构包括三层:
解析层:用于解析,验证,优化SQL语句,拆分混合SQL并最终生成查询计划;
计算层:用于将查询计划路由到特定执行计划,然后解释为给定存储或引擎的可执行代码(例如Elasticsearch JSON查询或Hive HQL);
存储层:用于准备数据提取和存储;
在大多数情况下,我们希望使用一种语言进行数据分析,并且不想考虑与数据分析无关的事物,Quicksql就是为此而生的。
Quicksql的目标是
涉及的表数据如下所示:
["Emc:0","MoVoiceCall:0","Mt:0","MoSig:2","MoData:1","HighPri:0","MoVideoCall:0","MoSms:0","MpsPri:0","McsPri:0"]
["Emc:0","MoVoiceCall:1","Mt:0","MoSig:26","MoData:1","HighPri:0","MoVideoCall:0","MoSms:0","Mp
cs231n assignment2 FC 解决python setup.py build_ext --inplace
Poshoi:
安装Postgresql报错The Database cluster initialisation failed.
Myron~mao:
cs231n assignment2 FC 解决python setup.py build_ext --inplace
Sentinel-2烦死:
SQL|一个字段存放在多个数组如何进行拆(替换sql数据源为hive)
Lansonli: