sparksql 和HQL的一些总结
- 需求：将下面的经纬计算geohash值，格式为 xx省xx市xx县，并写入mysql中
安徽省,34,000,1,117.123232,32.112345
阜阳市,341,34,2,117.123555,32.112444
阜南县,3412,341,3,117.123666,32.112777
+----------+---------+--------+--------+----+
| lng| lat|province|distinct|city|
+----------+---------+--------+--------+----+
|117.123666|32.112777| 安徽省| 阜阳市| 阜南县|
+----------+---------+--------+--------+----+
+------+--------+--------+----+
| geo|province|distinct|city|
+------+--------+--------+----+
|wteq9j| 安徽省| 阜阳市| 阜南县|
+------+--------+--------+----+``
package com.huang.sql
import java.util.Properties
import ch.hsr.geohash.GeoHash
import com.huang.sql.bean.information
import org.apache.spark.SparkConf
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, Dataset, SaveMode, SparkSession}
object sparksql {
def main(args: Array[String]): Unit = {
val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("test")
val spark: SparkSession = SparkSession.builder().config(conf).getOrCreate()
//RDD=>DataFrame=>DataSet转换需要引入隐式转换规则，否则无法转换
import spark.implicits._
//spark不是包名，是上下文环境对象名
val data: Dataset[String] = spark.read.textFile("input/test.txt")
val ds = data.map(x => x.split(",")).map(x => information(x(0), x(1).toInt, x(2).toInt, x(3).toInt, x(4).toDouble, x(5).toDouble))
ds.createOrReplaceTempView("test")
//定义udf
val geoo = (lng:Double,lat:Double)=>GeoHash.geoHashStringWithCharacterPrecision(lat,lng,6)
spark.udf.register("gpsTOhash",geoo)
// spark.udf.register("gpsTOhash",)
//spark.sql("select * from test").show()
val res = spark.sql(
"""
|select
|gpsTOhash(d.lng,d.lat) as geo,p.province as province,c.province as distinct,d.province as city
|from test d join test c on
|d.level = 3 and c.level=2 and d.parentID = c.id
|join test p on p.level =1 and c.parentID = p.id
|""".stripMargin)
res.show()
res.write
.format("jdbc")
.option("url", "jdbc:mysql://localhost:3306/mydb1?characterEcoding=utf-8&serverTimezone=GMT%2B8")
.option("user", "root")
.option("password", "huang100")
.option("dbtable", "dist")
.save()
// val props: Properties = new Properties()
// props.setProperty("user", "root")
// props.setProperty("password", "123456")
spark.close()
}
}
//如果表名和字段名冲突会发生Exception in thread “main” com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: mysql TEXT ou have an error in your SQL syntax; check the manual that corresponds to your MySQL
2.写入redis
val result: DStream[(String, Iterable[(String, Long)])] = wordToone.reduceByKey(_ + _).map { case ((adid, hourminute), count) => (adid, (hourminute, count)) }.groupByKey()
result.print(1000)
result.foreachRDD(rdd=>{
rdd.foreachPartition(it=>{
if (it.nonEmpty){ //判断是否有下一个元素，指针不会移动跳过这些元素
//写到redis
val client: Jedis = RedisUtil.getJedisClient
client.auth("huang100")
//批量写入 hmset
//key写死，value使用hash，，这样只有一个key了
val key = "last:hour:minute:counnt"
import org.json4s.JsonDSL._
val map: Map[String, String] = it.toMap.map {
case (key, it) => (key, JsonMethods.compact(JsonMethods.render(it.toList)))
}
//将scala的map转换为java的map
import scala.collection.JavaConversions._
println(map)
client.hmset(key,map)
client.close()
}
})
})
3、
-
.查询出来所有的点击记录, 并与 city_info 表连接, 得到每个城市所在的地区. 与 Product_info 表连接得到产品名称
-
按照地区和商品 id 分组, 统计出每个商品在每个地区的总点击次数
-
每个地区内按照点击次数降序排列 只取前三名. 并把结果保存在数据库中
select * from ( select * ,rank() over(partition by area order by click_count desc) as rank from ( select area, product_name, count(*) as click_count from ( select u.*, c.area, p.product_name from user_visit_action u join city_info c on u.city_id = c.city_id join product_info p on u.click_product_id = p.product_id where u.click_product_id >-1 )t1 group by area, product_name )t2 )t3 where rank<=3;
HSQL和sparksql的自定义函数
2、用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
一进一出
（2）UDAF（User-Defined Aggregation Function）
聚集函数，多进一出
类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
一进多出
如lateral view explore()
1、hive中需要继承udf，打成jar包上传到服务器
public class Lower extends UDF {
public String evaluate (final String s) {
if (s == null) {
return null;
}
return s.toLowerCase();
}
}
create temporary function mylower as "com.atguigu.hive.Lower";
select ename, mylower(ename) lowername from emp;
2、
-hive
- 自定义UDF：继承UDF，重写evaluate方法
- 自定义UDTF：继承自GenericUDTF，重写3个方法：initialize(自定义输出的列名和类型)，process（将结果返回forward(result)），close
- 为什么要自定义UDF/UDTF，因为自定义函数，可以自己埋点Log打印日志，出错或者数据异常，方便调试.
spark
udf比较简单，SparkSession.udf.register 方法进行注册
spark的udaf复杂，下面是个简单的例子:计算年龄的平均值，区内聚合之后再区外聚合
|MyAveragUDAF(com.huang.sql.sql_udf$User)|
±---------------------------------------+
| 26.0|
±---------------------------------------+
class MyAveragUDAF extends Aggregator[User,AvgBuffer,Double]{
override def zero: AvgBuffer = AvgBuffer(0L,0L)
override def reduce(b: AvgBuffer, a: User): AvgBuffer = {
b.sum = b.sum + a.age
b.count = b.count + 1
b
}
override def merge(b1: AvgBuffer, b2: AvgBuffer): AvgBuffer = {
b1.sum = b1.sum + b2.sum
b1.count = b1.count + b2.count
b1
}
override def finish(reduction: AvgBuffer): Double = {
reduction.sum.toDouble / reduction.count
}
override def bufferEncoder: Encoder[AvgBuffer] = {
Encoders.product
}
override def outputEncoder: Encoder[Double] = {
Encoders.scalaDouble
}
}
val sparkconf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("wordcount")
val spark: SparkSession = SparkSession.builder().config(sparkconf).getOrCreate()
//spark不是包名，是上下文环境对象名
//RDD=>DataFrame=>DataSet转换需要引入隐式转换规则，否则无法转换
import spark.implicits._
val rdd: RDD[(Int, String, Long)] = spark.sparkContext.makeRDD(List((1,"zhangsan",30L),(2,"lisi",28L),(3,"wangwu",20L)))
val userRDD: RDD[User] = rdd.map { case (id, name, age) => {
User(id, name, age)
}
}
val ds: Dataset[User] = userRDD.toDS()
ds.createOrReplaceTempView("user")
val myAverage = new MyAveragUDAF
val column: TypedColumn[User, Double] = myAverage.toColumn
ds.select(column).show()
简述SparkSQL中RDD、DataFrame、DataSet三者的区别与联系?
1）RDD
优点:
编译时类型安全
编译时就能检查出类型错误
面向对象的编程风格
直接通过类名点的方式来操作数据
缺点:
序列化和反序列化的性能开销
无论是集群间的通信, 还是IO操作都需要对对象的结构和数据进行序列化和反序列化。
GC的性能开销，频繁的创建和销毁对象, 势必会增加GC
2）DataFrame
DataFrame引入了schema和off-heap
schema : RDD每一行的数据, 结构都是一样的，这个结构就存储在schema中。 Spark通过schema就能够读懂数据, 因此在通信和IO时就只需要序列化和反序列化数据, 而结构的部分就可以省略了。
3）DataSet
DataSet结合了RDD和DataFrame的优点，并带来的一个新的概念Encoder。
当序列化数据时，Encoder产生字节码与off-heap进行交互，能够达到按需访问数据的效果，而不用反序列化整个对象。Spark还没有提供自定义Encoder的API
hive的分区和分桶
hive的分区
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');
//查询
select * from dept_partition where month='201709'
union
select * from dept_partition where month='201708'
`//增加分区
alter table dept_partition add partition(month='201705') partition(month='201704');``
//二级分区
partitioned by (month string, day string)
关联的方式
dfs-mkdir -p
/user/hive/warehouse/dept_partition2/month=201709/day=12;
dfs -put /opt/module/datas/dept.txt
/user/hive/warehouse/dept_partition2/month=201709/day=12;
hive分桶
分区针对的是数据的存储路径；分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);
注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
- x表示从第几个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。
- 注意：x的值必须小于等于y的值
4个By区别
1）Sort By：分区内有序；
2）Order By：全局排序，只有一个Reducer；
3）Distrbute By：类似MR中Partition，进行分区，结合sort by使用。
4） Cluster By：当Distribute by和Sorts by字段相同时，可以使用Cluster by方式。Cluster by除了具有Distribute by的功能外还兼具Sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC
order by会引发全局排序
istribute by + sort by就是该替代方案，被distribute by设定的字段为KEY，
数据会被HASH分发到不同的reducer机器上，然后sort by会对同一个reducer机器上的每组数据进行局部排序
窗口函数，开窗就像对数据画范围，在范围内在做一些操作
参考：https://blog.csdn.net/Abysscarry/article/details/81408265
- RANK() 排序相同时会重复，总数不会变
- DENSE_RANK() 排序相同时会重复，总数会减少
- ROW_NUMBER() 会根据顺序计算
1） OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
2）CURRENT ROW：当前行
3）n PRECEDING：往前n行数据
4） n FOLLOWING：往后n行数据
5）UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
6） LAG(col,n)：往前第n行数据
7）LEAD(col,n)：往后第n行数据
8） NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
计算时间差：
select user_id ,data_dt,
datediff(data_dt,lag2) lag2_diff,
datediff(data_dt,lag1) lag1_diff,
datediff(data_dt,lead1) lead1_diff,
datediff(data_dt,lead2) lead2_diff
from
(select user_id, data_dt ,
lag(data_dt,2,'1990-07-01') over(partition by user_id order by data_dt)lag2,
lag(data_dt,1,'1990-07-01') over(partition by user_id order by data_dt)lag1,
lead(data_dt,1,'1990-07-01') over(partition by user_id order by data_dt)lead1,
lead(data_dt,2,'1990-07-01') over(partition by user_id order by data_dt)lead2
from
(select user_id ,date_format(regexp_replace(data_dt,'/','-'),'yyyy-MM-dd') data_dt
from user_low_carbon where substring(data_dt,1,4)='2017'
group by user_id,data_dt having sum(low_carbon)>100)t1)t2;
扫一扫
1391
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
