sql 去重并统计总数_使用SparkSql进行表的分析与统计

最新推荐文章于 2024-05-12 15:55:25 发布
最新推荐文章于 2024-05-12 15:55:25 发布
阅读量2k 收藏 3
点赞数 1
文章标签： sql 去重并统计总数 sql按小时统计总数 分析dmp文件使用表空间

背景

​ 我们的数据挖掘平台对数据统计有比较迫切的需求，而Spark本身对数据统计已经做了一些工作，希望梳理一下Spark已经支持的数据统计功能，后期再进行扩展。

准备数据

在参考文献6中下载https://archive.ics.uci.edu/ml/machine-learning-databases/iris/，此处格式为iris.data格式，先将data后缀改为csv后缀(不影响使用，只是为了保证后续操作不需要修改)。

数据格式如下：

d520ffd1bc2b8e0c50fc87d6dc87288d.png

鸢尾花数据格式

数据说明见附录中的鸢尾花数据。

我们先把数据放到Spark sql数仓中

CREATE TABLE IF NOT EXISTS iris ( SepalLength FLOAT , SepalWidth FLOAT   , PetalLength FLOAT , PetalWidth FLOAT   , Species VARCHAR(100) )ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','STORED AS TEXTFILELOCATION '/mnt/disk1/starqiu/iris';

表的分析与统计

Analyze Table语法如下：

ANALYZE TABLE [db_name.]table_name COMPUTE STATISTICS [analyze_option]

Collect statistics about the table that can be used by the query optimizer to find a better plan.

可以看到Spark表的分析可以为spark sql做查询优化，以便得到更好的查询性能。Spark Sql默认使用CBO(基于代价的优化)，这在多表join查询时尤其有用。

此处的analyze_option参数主要分为两类，表统计和列统计。

表统计

表的基本统计信息一般包括记录总数和所占空间。

Table statistics用法如下：

ANALYZE TABLE [db_name.]table_name COMPUTE STATISTICS [NOSCAN]

Collect only basic statistics for the table (number of rows, size in bytes).

NOSCAN

Collect only statistics that do not require scanning the whole table (that is, size in bytes).

运行命令ANALYZE TABLE iris COMPUTE STATISTICS;可以得到表的记录总数和所占空间大小。如果不想全表扫描，加上NOSCAN关键字，不会全表扫描，但只能得到所占空间大小。

表统计信息的描述命令语法如下：

DESCRIBE [EXTENDED] [db_name.]table_name

Return the metadata of an existing table (column names, data types, and comments). If the table does not exist, an exception is thrown.

EXTENDED

Display detailed information about the table, including parent database, table type, storage information, and properties.

Describe Partition

运行DESCRIBE EXTENDED iris;，结果如下：

spark-sql> DESCRIBE EXTENDED iris;SepalLength    float    NULLSepalWidth    float    NULLPetalLength    float    NULLPetalWidth    float    NULLSpecies    string    NULL        # Detailed Table Information    CatalogTable(    Table: `default`.`iris`    Owner: root    Created: Sat Feb 16 17:24:32 CST 2019    Last Access: Thu Jan 01 08:00:00 CST 1970    Type: EXTERNAL    Schema: [StructField(SepalLength,FloatType,true), StructField(SepalWidth,FloatType,true), StructField(PetalLength,FloatType,true), StructField(PetalWidth,FloatType,true), StructField(Species,StringType,true)]    Provider: hive    Properties: [rawDataSize=-1, numFiles=0, transient_lastDdlTime=1550311815, totalSize=0, COLUMN_STATS_ACCURATE=false, numRows=-1]    Statistics: sizeInBytes=3808, rowCount=150, isBroadcastable=false    Storage(Location: hdfs://data126:8020/mnt/disk1/starqiu/iris, InputFormat: org.apache.hadoop.mapred.TextInputFormat, OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, Serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Properties: [field.delim=,, serialization.format=,])    Partition Provider: Catalog)    Time taken: 0.112 seconds, Fetched 7 row(s)

通过Statistics:可以看到表的记录总数是150条，所占空间3808B，约4KB。

列统计

Column statistics用法如下：

ANALYZE TABLE [db_name.]table_name COMPUTE STATISTICS FOR COLUMNS col1 [, col2, ...]

Collect column statistics for the specified columns in addition to table statistics.

Tip

Use this command whenever possible because it collects more statistics so the optimizer can find better plans. Make sure to collect statistics for all columns used by the query.

列统计的描述命令语法如下：

DESCRIBE [EXTENDED][db_name.]table_name column_nameNew in version runtime-3.3.

EXTENDED

Display detailed information about the specified columns, including the column statistics collected by the command ANALYZE TABLE tablename COMPUTE STATISTICS FOR COLUMNS columnname [column_name, ...].

需要注意的是这个功能在runtime-3.3版本才有的特性，而runtime-3.3封装的是Spark 2.2，会详见文末附录的databricks Runtime版本与Spark版本的对应关系

运行命令ANALYZE TABLE iris COMPUTE STATISTICS FOR COLUMNS SepalLength, SepalWidth, PetalLength, PetalWidth, Species;计算指定多列的统计信息，

运行DESCRIBE EXTENDED iris SepalLength;获取指定一列的统计信息，结果如下：

spark-sql> ANALYZE TABLE iris COMPUTE STATISTICS FOR COLUMNS SepalLength, SepalWidth, PetalLength, PetalWidth, Species;Time taken: 4.45 secondsspark-sql> DESCRIBE EXTENDED iris PetalWidth;col_name    PetalWidthdata_type    floatcomment    NULLmin    0.10000000149011612max    2.5num_nulls    0distinct_count    21avg_col_len    4max_col_len    4histogram    NULLTime taken: 0.104 seconds, Fetched 10 row(s)

目前测试Spark2.2.2不支持该语句，但是Spark2.4.0支持。如果不支持，则可以通过访问hive的元数据库也可以得到这些信息，sql语句如下：

select param_key, param_value from TABLE_PARAMS tp, TBLS t where tp.tbl_id=t.tbl_id and tbl_name = 'iris' and param_key like 'spark.sql.stat%';

以下是PetalWidth列的统计结果，可以看到包含不重复的记录数，空值数，最大值、最小值，平均长度以及最大长度

c2d21bb7d0702ea2f8e931b34f7cfd32.png

PetalWidth统计结果

总结

​ 可以看到这些统计信息不仅对了解数据质量非常有用，对使用Spark sql进行查询也能得到优化，进一步提升速度。后续再写一篇CBO如何利用这些信息进行优化。

​ 目前还不清楚Runtime中的Spark功能和开源版的有无差异，但Spark2.4支持表的分析统计操作，建议平台后续项目升级到Spark2.4 。

附录

鸢尾花数据说明

​ Iris数据集是常用的分类实验数据集，由Fisher, 1936收集整理。Iris也称鸢尾花卉数据集，是一类多重变量分析的数据集。数据集包含150个数据集，分为3类，每类50个数据，每个数据包含4个属性。iris以鸢尾花的特征作为数据来源，常用在分类操作中。该数据集由3种不同类型的鸢尾花的50个样本数据构成。其中的一个种类与另外两个种类是线性可分离的，后两个种类是非线性可分离的。

四个属性：

Sepal.Length(花萼长度)，单位是cm;

Sepal.Width(花萼宽度)，单位是cm;

Petal.Length(花瓣长度)，单位是cm;

Petal.Width(花瓣宽度)，单位是cm;

三个种类：

Iris Setosa(山鸢尾)；

Iris Versicolour(杂色鸢尾)；

Iris Virginica(维吉尼亚鸢尾)。

databricks Runtime

96091104138b69be020696e538e40f7a.png

Runtime是databricks 统一分析平台的一部分，官网描述如下：

Accelerate innovation by unifying data science, engineering and business, with the Databricks Unified Analytics Platform, from the original creators of Apache Spark™.

Runtime的描述如下：

Simplify operations and get up to 50x better performance with cloud-optimized Apache Spark™.

可以看到主要是基于云优化来简化操作并提升50倍以上的性能。

id6)

Current Releases

96d45cf3849b3a0338fe4a1e83668363.png

当前发布版本对应关系

Marked for Deprecation

e0a41f82d1fd470740072008cd6c5494.png

标记废弃的发布版本对应关系

Deprecated Releases

260b214607e7265d44a0cf7a2bc9db45.png

已废弃的发布版本对应关系

参考文献

  1. https://docs.databricks.com/spark/latest/spark-sql/language-manual/analyze-table.html
  2. https://docs.databricks.com/spark/latest/spark-sql/language-manual/describe-table.html
  3. https://docs.databricks.com/spark/latest/spark-sql/cbo.html
  4. https://docs.databricks.com/release-notes/runtime/databricks-runtime-ver.html#versioning
  5. https://blog.csdn.net/Albert201605/article/details/82313139
  6. https://archive.ics.uci.edu/ml/datasets/Iris

本文由博客一文多发平台 https://openwrite.cn?from=article_bottom 发布！

weixin_39788382
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
scala spark 数据对比_Spark 实践——用 Scala 和 Spark 进行数据分析
weixin_39736934的博客
12-22 285
1.获取数据集数据集来自加州大学欧文分校机器学习资料库(UC Irvine Machine Learning Repository)，这个资料库为研究和教学提供了大量非常好的数据源， 这些数据源非常有意义，并且是免费的。 我们要分析的数据集来源于一项记录关联研究，这项研究是德国一家医院在 2010 年完成的。这个数据集包含数百万对病人记录，每对记录都根据不同标准来匹配，比如病人姓名(名字和姓氏)、...
Spark SQL案例：统计每日新增用户
weixin_52112640的博客
07-08 793
现需要根据上述数据统计每日新增的用户数量，期望统计结果 即2022-01-01新增了3个用户（分别为mike、alice、brown），2022-01-02新增了1个用户（green），2022-01-03新增了两个用户（分别为smith、brian）。 预备工作：启动集群的HDFS与Spark...
spark写表指定外部表_Spark SQL 之自定义删除外部表
weixin_39615984的博客
12-21 177
前言Spark SQL 在删除外部表时，本不能删除外部表的数据的。本篇文章主要介绍如何修改Spark SQL 源码实现在删除外部表的时候，可以带额外选项来删除外部表的数据。本文的环境是我一直使用的 spark 2.4.3 版本。1. 修改ANTLR4 语法文件修改 SqlBase.g4文件中drop Table 相关语句，添加(WITH DATA)?， 修改完之后如下：DROP TABLE (IF...
最新【Spark】Spark SQL 数据类型转换_spark 字段类型，2024年最新大数据开发最新实习面试经验总结
最新发布
2401_84167086的博客
05-12 809
只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**
SparkSQL中创建外部表及使用
GulfMoon
06-16 8606
一、使用需求 工作中经常会需要与外围系统打交道，由于外围系统和本系统不处于同一个Hadoop集群下，且不具有访问本系统的权限，所以基本上大数据量的接口都是以文件的方式进行传输。如何快速、便捷的将文件入Spark库中？ 通过SparkSQL中创建外部表的方式就能够很好地解决这一需求。 二、解决方案 1. hdfs上创建存放外部表数据文件的目录 hdfs dfs -mkdir -p /hu...
Spark SQL explaind中的统计信息-深入了解CBO优化
monkeyboy_tech的博客
04-19 1872
本文翻译自Statistic in Spark SQl explained Spark SQL 优化器使用两种优化方式：基于规则的和基于代价的。前者依赖于启发式规则，而后者依赖于数据的统计属性。在这篇文章里,我们解释一下在底层这些统计信息是怎么被用到，以及哪些场景下是有用的，并且怎么来使用这些统计信息。 大部分基于启发式的优化规则都没有考虑到被处理的数据属性。比如：基于启发式的PredicatePushDown规则就是基于先过滤再计算的假设。 然而有些场景spark能够通过数据的统计信息来得出更好的计划，这
Sql Server 分组统计并合计总数及WITH ROLLUP应用
09-10
在SQL Server中，分组统计是一项基础且重要的功能，它允许我们按照特定列的值对数据进行聚合，以便分析和汇总信息。`GROUP BY`语句是实现这一目标的关键，而`WITH ROLLUP`是扩展这个功能的一种方法，特别适用于生成...
SQL Server中统计每个表行数的快速方法
09-10
在SQL Server中，统计每个表的行数是数据库管理员经常需要进行的一项操作，这对于数据库维护、性能优化以及容量规划都非常重要。通常，我们会使用`COUNT()`聚合函数来获取表的行数，例如`SELECT COUNT(*) FROM ...
SQL一条语句统计记录总数及各状态数
09-10
在SQL查询中，有时我们需要快速地获取数据表中记录的总数以及按照特定字段（如状态）分类的计数。在给定的标题和描述中，我们关注的是如何使用单条SQL语句完成这个任务。这里提供了两种不同的方法，都是针对记录总数...
sql_按照某一个字段进行去重后获取全部字段
07-10
sql语句：按照某一个字段进行去重后获取全部字段。
SQLSERVER收集语句运行的统计信息并进行分析
01-21
如果DBA能够把问题语句单独测试运行，可以在运行前打开下面这三个开关，收集语句运行的统计信息。 这些信息对分析问题很有价值。 代码如下: SET STATISTICS TIME ON SET STATISTICS IO ON SET STATISTICS PROFILE ON...
Spark实现用户订单数据表连接
11-19
Spark实现用户订单数据表连接，实现了用户信息表和订单信息表的内连接操作
Spark SQL 查询人口数量与平均年龄实验
weixin_34054931的博客
07-29 1318
案例： 查询某地的人口数量与平均年龄；分析:使用Spark查询人口的数量和平均年龄，首先我们使用Spark SQL 方式查询，将原始数据读入，将其转化为DATAFRAME，然后是SQL方式计算：准备实验数据代码如下：objectdemo{ defmain(args:Array[String]):Unit={ valwrite=newFileW...
spark on yarn 完全分部署_从零开始认识 Spark
weixin_39775577的博客
11-26 261
前言 Spark的知识点很多，决定分多P来慢慢讲 ，比较关键的RDD算子其实已经写了大半，奈何内容还是太多了就不和这篇扯皮的放一起了。 老套路，我们点开官网来see see先吧 把这句话翻译一下 spark是在Hadoop基础上的改进，是 UC Berkeley AMP lab 所开源的类 Hadoop MapReduce 的通用的并行计算框架，Spark 基于 mapReduce 算法实现的分布...
SPARK-SQL - 创建和操作内部表，外部表
小哇
08-11 3789
注意：以下操作中，内部表和外部表使用的数据都是parquet格式的数据，其它类型的数据不行 示例代码 import org.apache.spark.sql.Dataset; import org.apache.spark.sql.Row; import org.apache.spark.sql.SparkSession; public class test19 { public static void main(String[] args) throws Exception {
SPARK统计信息的来源-通过优化规则来分析
monkeyboy_tech的博客
03-23 1800
背景 此文的分析基于spark 3.1.2 且set spark.sql.catalogImplementation = hive 且表是分区的情况下 在之前翻译的文章Spark SQL explaind中的统计信息-深入了解CBO优化里，我们说到,如果一个hive表是分区的，没有开启CBO，没有进行ATC，那么该逻辑计划的sizeInBytes就是8EB。其实这是不对的。我来分析一下。 分析 就如前面的图所示： 这只是一个大概的流程，在spark的实现中，是有细微的区别的（至少在spark 3.1.2是
关于Spark SQL外部表在实战中遇到的问题
GulfMoon
07-15 1041
笔者之前写过一篇关于Spark SQL外部表的比较全面的学习实验博客，参https://blog.csdn.net/u011817217/article/details/92403843 本篇旨在描述和总结在实际工作中使用Spark SQL外部表遇到的一些问题以及相应的解决方案。 一、需求描述 外围系统通过文件接口的方式定期给一份数据文件，关于接口描述和数据文件示例如下： 1. 文件接口...
去重再统计总数
weixin_45427945的博客
09-01 54
去重在统计总数
Spark sql自定义函数UDF，处理column值
linshow的专栏
01-27 1227
spark自定义函数UDF，处理DataFrame中列值，并返回新列。 如果只是修改列值，在withColumn(“newName”,f=>col)，将将列newName替换为原始名称即可。 import org.apache.spark.sql.{DataFrame, SparkSession} import org.apache.spark.sql.functions._ object TestSql { def main(args: Array[String]): Unit = {
sqlserver 去重在统计
06-13
如果您要在 SQL Server 中统计去重后的数据，可以使用 DISTINCT 关键字来实现，例如： ``` SELECT COUNT(DISTINCT column_name) FROM table_name; ``` 其中，column_name 是要去重的列名，table_name 是要统计的表名。这条 SQL 语句会返回去重后的行数。 如果您需要统计多个列的去重行数，可以在 COUNT 函数中使用多个 DISTINCT 关键字，例如： ``` SELECT COUNT(DISTINCT column_name1, column_name2) FROM table_name; ``` 这条 SQL 语句会返回 column_name1 和 column_name2 去重后的行数。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值