spark sql常用方法

最新推荐文章于 2024-05-18 21:08:54 发布
最新推荐文章于 2024-05-18 21:08:54 发布
阅读量446 收藏 3
点赞数 1
分类专栏： spark 文章标签： spark sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010711495/article/details/110083125
版权

常用方法

show

scala> emp.show()
+----+------+-----+------+----------+---------+----+------+
|comm|deptno|empno| ename|  hiredate|      job| mgr|   sal|
+----+------+-----+------+----------+---------+----+------+
|null|    20| 7369| SMITH|1980-12-17|    CLERK|7902| 800.0|
| 300|    30| 7499| ALLEN|1981-02-20| SALESMAN|7698|1600.5|
| 500|    30| 7521|  WARD|1981-02-22| SALESMAN|7698|1250.0|
|null|    20| 7566| JONES|1981-04-02|  MANAGER|7839|2975.0|
|1400|    30| 7654|MARTIN|1981-09-28| SALESMAN|7698|1250.0|
|null|    30| 7698| BLAKE|1981-05-01|  MANAGER|7839|2850.0|
|null|    10| 7782| CLARK|1981-06-09|  MANAGER|7839|2450.0|
|null|    20| 7788| SCOTT|1987-04-19|  ANALYST|7566|3000.0|
|null|    10| 7839|  KING|1981-11-17|PRESIDENT|null|5000.0|
|   0|    30| 7844|TURNER|1981-09-08| SALESMAN|7698|1500.0|
|null|    20| 7876| ADAMS|1987-05-23|    CLERK|7788|1100.0|
|null|    30| 7900| JAMES|1981-12-03|    CLERK|7698| 950.0|
|null|    20| 7902|  FORD|1981-12-02|  ANALYST|7566|3000.0|
|null|    10| 7934|MILLER|1982-01-23|    CLERK|7369|1300.0|
+----+------+-----+------+----------+---------+----+------+


scala> emp.show(3)
+----+------+-----+-----+----------+--------+----+------+
|comm|deptno|empno|ename|  hiredate|     job| mgr|   sal|
+----+------+-----+-----+----------+--------+----+------+
|null|    20| 7369|SMITH|1980-12-17|   CLERK|7902| 800.0|
| 300|    30| 7499|ALLEN|1981-02-20|SALESMAN|7698|1600.5|
| 500|    30| 7521| WARD|1981-02-22|SALESMAN|7698|1250.0|
+----+------+-----+-----+----------+--------+----+------+
only showing top 3 rows

collect

scala> emp.collect
res8: Array[org.apache.spark.sql.Row] = Array([null,20,7369,SMITH,1980-12-17,CLERK,7902,800.0], [300,30,7499,ALLEN,1981-02-20,SALESMAN,7698,1600.5], [500,30,7521,WARD,1981-02-22,SALESMAN,7698,1250.0], [null,20,7566,JONES,1981-04-02,MANAGER,7839,2975.0], [1400,30,7654,MARTIN,1981-09-28,SALESMAN,7698,1250.0], [null,30,7698,BLAKE,1981-05-01,MANAGER,7839,2850.0], [null,10,7782,CLARK,1981-06-09,MANAGER,7839,2450.0], [null,20,7788,SCOTT,1987-04-19,ANALYST,7566,3000.0], [null,10,7839,KING,1981-11-17,PRESIDENT,null,5000.0], [0,30,7844,TURNER,1981-09-08,SALESMAN,7698,1500.0], [null,20,7876,ADAMS,1987-05-23,CLERK,7788,1100.0], [null,30,7900,JAMES,1981-12-03,CLERK,7698,950.0], [null,20,7902,FORD,1981-12-02,ANALYST,7566,3000.0], [null,10,7934,MILLER,1982-01-23,CLERK,7369,1300.0])

collectAsList

scala> emp.collectAsList
res9: java.util.List[org.apache.spark.sql.Row] = [[null,20,7369,SMITH,1980-12-17,CLERK,7902,800.0], [300,30,7499,ALLEN,1981-02-20,SALESMAN,7698,1600.5], [500,30,7521,WARD,1981-02-22,SALESMAN,7698,1250.0], [null,20,7566,JONES,1981-04-02,MANAGER,7839,2975.0], [1400,30,7654,MARTIN,1981-09-28,SALESMAN,7698,1250.0], [null,30,7698,BLAKE,1981-05-01,MANAGER,7839,2850.0], [null,10,7782,CLARK,1981-06-09,MANAGER,7839,2450.0], [null,20,7788,SCOTT,1987-04-19,ANALYST,7566,3000.0], [null,10,7839,KING,1981-11-17,PRESIDENT,null,5000.0], [0,30,7844,TURNER,1981-09-08,SALESMAN,7698,1500.0], [null,20,7876,ADAMS,1987-05-23,CLERK,7788,1100.0], [null,30,7900,JAMES,1981-12-03,CLERK,7698,950.0], [null,20,7902,FORD,1981-12-02,ANALYST,7566,3000.0], [null,10,7934,MILLER,1982-01-23,CLERK,7369,1300.0]]

first等

scala> emp.first
res10: org.apache.spark.sql.Row = [null,20,7369,SMITH,1980-12-17,CLERK,7902,800.0]

scala> emp.head
res11: org.apache.spark.sql.Row = [null,20,7369,SMITH,1980-12-17,CLERK,7902,800.0]

scala> emp.head(2)
res12: Array[org.apache.spark.sql.Row] = Array([null,20,7369,SMITH,1980-12-17,CLERK,7902,800.0], [300,30,7499,ALLEN,1981-02-20,SALESMAN,7698,1600.5])

scala> emp.take(2)
res13: Array[org.apache.spark.sql.Row] = Array([null,20,7369,SMITH,1980-12-17,CLERK,7902,800.0], [300,30,7499,ALLEN,1981-02-20,SALESMAN,7698,1600.5])

scala> emp.takeAsList(2)
res14: java.util.List[org.apache.spark.sql.Row] = [[null,20,7369,SMITH,1980-12-17,CLERK,7902,800.0], [300,30,7499,ALLEN,1981-02-20,SALESMAN,7698,1600.5]]

where

scala> emp.where("deptno=10").show
+----+------+-----+------+----------+---------+----+------+
|comm|deptno|empno| ename|  hiredate|      job| mgr|   sal|
+----+------+-----+------+----------+---------+----+------+
|null|    10| 7782| CLARK|1981-06-09|  MANAGER|7839|2450.0|
|null|    10| 7839|  KING|1981-11-17|PRESIDENT|null|5000.0|
|null|    10| 7934|MILLER|1982-01-23|    CLERK|7369|1300.0|
+----+------+-----+------+----------+---------+----+------+


scala> emp.where("deptno=10 and sal>1400").show
+----+------+-----+-----+----------+---------+----+------+
|comm|deptno|empno|ename|  hiredate|      job| mgr|   sal|
+----+------+-----+-----+----------+---------+----+------+
|null|    10| 7782|CLARK|1981-06-09|  MANAGER|7839|2450.0|
|null|    10| 7839| KING|1981-11-17|PRESIDENT|null|5000.0|
+----+------+-----+-----+----------+---------+----+------+

filter

scala> emp.filter("deptno=10 and sal>1400").show
+----+------+-----+-----+----------+---------+----+------+
|comm|deptno|empno|ename|  hiredate|      job| mgr|   sal|
+----+------+-----+-----+----------+---------+----+------+
|null|    10| 7782|CLARK|1981-06-09|  MANAGER|7839|2450.0|
|null|    10| 7839| KING|1981-11-17|PRESIDENT|null|5000.0|
+----+------+-----+-----+----------+---------+----+------+

select

scala> emp.select("empno","ename")show(3)
+-----+-----+
|empno|ename|
+-----+-----+
| 7369|SMITH|
| 7499|ALLEN|
| 7521| WARD|
+-----+-----+
only showing top 3 rows


scala> emp.select(emp("empno"),emp("sal")+1).show(3)
+-----+---------+
|empno|(sal + 1)|
+-----+---------+
| 7369|    801.0|
| 7499|   1601.5|
| 7521|   1251.0|
+-----+---------+
only showing top 3 rows


scala> emp.select(col("sal"),col("sal")+1).show(3)
+------+---------+
|   sal|(sal + 1)|
+------+---------+
| 800.0|    801.0|
|1600.5|   1601.5|
|1250.0|   1251.0|
+------+---------+
only showing top 3 rows

selectExpr

scala> emp.selectExpr("ename","empno as no","round(sal)").show(4)
+-----+----+-------------+
|ename|  no|round(sal, 0)|
+-----+----+-------------+
|SMITH|7369|        800.0|
|ALLEN|7499|       1601.0|
| WARD|7521|       1250.0|
|JONES|7566|       2975.0|
+-----+----+-------------+
only showing top 4 rows

col

scala> emp.select(col("ename")).show
+------+
| ename|
+------+
| SMITH|
| ALLEN|
|  WARD|
| JONES|
|MARTIN|
| BLAKE|
| CLARK|
| SCOTT|
|  KING|
|TURNER|
| ADAMS|
| JAMES|
|  FORD|
|MILLER|
+------+

drop

scala> emp.drop("empno").show(3)
+----+------+-----+----------+--------+----+------+
|comm|deptno|ename|  hiredate|     job| mgr|   sal|
+----+------+-----+----------+--------+----+------+
|null|    20|SMITH|1980-12-17|   CLERK|7902| 800.0|
| 300|    30|ALLEN|1981-02-20|SALESMAN|7698|1600.5|
| 500|    30| WARD|1981-02-22|SALESMAN|7698|1250.0|
+----+------+-----+----------+--------+----+------+
only showing top 3 rows

limit

scala> emp.limit(3)
res26: org.apache.spark.sql.Dataset[org.apache.spark.sql.Row] = [comm: bigint, deptno: bigint ... 6 more fields]

scala> emp.limit(3).show
+----+------+-----+-----+----------+--------+----+------+
|comm|deptno|empno|ename|  hiredate|     job| mgr|   sal|
+----+------+-----+-----+----------+--------+----+------+
|null|    20| 7369|SMITH|1980-12-17|   CLERK|7902| 800.0|
| 300|    30| 7499|ALLEN|1981-02-20|SALESMAN|7698|1600.5|
| 500|    30| 7521| WARD|1981-02-22|SALESMAN|7698|1250.0|
+----+------+-----+-----+----------+--------+----+------+

orderBy & sort

scala> emp.orderBy(-col("sal")).show(3)
+----+------+-----+-----+----------+---------+----+------+
|comm|deptno|empno|ename|  hiredate|      job| mgr|   sal|
+----+------+-----+-----+----------+---------+----+------+
|null|    10| 7839| KING|1981-11-17|PRESIDENT|null|5000.0|
|null|    20| 7788|SCOTT|1987-04-19|  ANALYST|7566|3000.0|
|null|    20| 7902| FORD|1981-12-02|  ANALYST|7566|3000.0|
+----+------+-----+-----+----------+---------+----+------+
only showing top 3 rows


scala> emp.sort(-col("sal")).show(3)
+----+------+-----+-----+----------+---------+----+------+
|comm|deptno|empno|ename|  hiredate|      job| mgr|   sal|
+----+------+-----+-----+----------+---------+----+------+
|null|    10| 7839| KING|1981-11-17|PRESIDENT|null|5000.0|
|null|    20| 7788|SCOTT|1987-04-19|  ANALYST|7566|3000.0|
|null|    20| 7902| FORD|1981-12-02|  ANALYST|7566|3000.0|
+----+------+-----+-----+----------+---------+----+------+
only showing top 3 rows

sortWithinPartitions

scala> emp.repartition(2).sortWithinPartitions("sal").show
+----+------+-----+------+----------+---------+----+------+
|comm|deptno|empno| ename|  hiredate|      job| mgr|   sal|
+----+------+-----+------+----------+---------+----+------+
|null|    20| 7369| SMITH|1980-12-17|    CLERK|7902| 800.0|
|null|    20| 7876| ADAMS|1987-05-23|    CLERK|7788|1100.0|
|1400|    30| 7654|MARTIN|1981-09-28| SALESMAN|7698|1250.0|
| 500|    30| 7521|  WARD|1981-02-22| SALESMAN|7698|1250.0|
|null|    10| 7934|MILLER|1982-01-23|    CLERK|7369|1300.0|
|null|    20| 7566| JONES|1981-04-02|  MANAGER|7839|2975.0|
|null|    20| 7788| SCOTT|1987-04-19|  ANALYST|7566|3000.0|
|null|    30| 7900| JAMES|1981-12-03|    CLERK|7698| 950.0|
|   0|    30| 7844|TURNER|1981-09-08| SALESMAN|7698|1500.0|
| 300|    30| 7499| ALLEN|1981-02-20| SALESMAN|7698|1600.5|
|null|    10| 7782| CLARK|1981-06-09|  MANAGER|7839|2450.0|
|null|    30| 7698| BLAKE|1981-05-01|  MANAGER|7839|2850.0|
|null|    20| 7902|  FORD|1981-12-02|  ANALYST|7566|3000.0|
|null|    10| 7839|  KING|1981-11-17|PRESIDENT|null|5000.0|
+----+------+-----+------+----------+---------+----+------+

groupBy
scala> emp.groupBy(“deptno”).count.show

+------+-----+
|deptno|count|
+------+-----+
|    10|    3|
|    30|    6|
|    20|    5|
+------+-----+

cube

scala> emp.cube("deptno","job").sum("sal").show()
+------+---------+--------+
|deptno|      job|sum(sal)|
+------+---------+--------+
|    20|  MANAGER|  2975.0|
|  null|PRESIDENT|  5000.0|
|  null|     null| 29025.5|
|    10|PRESIDENT|  5000.0|
|  null| SALESMAN|  5600.5|
|    30|    CLERK|   950.0|
|    10|     null|  8750.0|
|    20|    CLERK|  1900.0|
|  null|  ANALYST|  6000.0|
|    30| SALESMAN|  5600.5|
|    20|     null| 10875.0|
|    10|    CLERK|  1300.0|
|  null|  MANAGER|  8275.0|
|    30|     null|  9400.5|
|  null|    CLERK|  4150.0|
|    20|  ANALYST|  6000.0|
|    30|  MANAGER|  2850.0|
|    10|  MANAGER|  2450.0|
+------+---------+--------+

rollup

scala> emp.rollup("deptno","job").sum("sal").show()
+------+---------+--------+
|deptno|      job|sum(sal)|
+------+---------+--------+
|    20|  MANAGER|  2975.0|
|  null|     null| 29025.5|
|    10|PRESIDENT|  5000.0|
|    30|    CLERK|   950.0|
|    10|     null|  8750.0|
|    20|    CLERK|  1900.0|
|    30| SALESMAN|  5600.5|
|    20|     null| 10875.0|
|    10|    CLERK|  1300.0|
|    30|     null|  9400.5|
|    20|  ANALYST|  6000.0|
|    30|  MANAGER|  2850.0|
|    10|  MANAGER|  2450.0|
+------+---------+--------+

max

scala> emp.groupBy("deptno").max("sal").show()
+------+--------+
|deptno|max(sal)|
+------+--------+
|    10|  5000.0|
|    30|  2850.0|
|    20|  3000.0|
+------+--------+

distinct

scala> emp.distinct.show(3)
+----+------+-----+-----+----------+-------+----+------+
|comm|deptno|empno|ename|  hiredate|    job| mgr|   sal|
+----+------+-----+-----+----------+-------+----+------+
|null|    20| 7876|ADAMS|1987-05-23|  CLERK|7788|1100.0|
|null|    20| 7902| FORD|1981-12-02|ANALYST|7566|3000.0|
|null|    20| 7788|SCOTT|1987-04-19|ANALYST|7566|3000.0|
+----+------+-----+-----+----------+-------+----+------+
only showing top 3 rows

dropDuplicates

scala> emp.dropDuplicates("deptno").show
+----+------+-----+-----+----------+--------+----+------+
|comm|deptno|empno|ename|  hiredate|     job| mgr|   sal|
+----+------+-----+-----+----------+--------+----+------+
|null|    10| 7782|CLARK|1981-06-09| MANAGER|7839|2450.0|
| 300|    30| 7499|ALLEN|1981-02-20|SALESMAN|7698|1600.5|
|null|    20| 7369|SMITH|1980-12-17|   CLERK|7902| 800.0|
+----+------+-----+-----+----------+--------+----+------+

union

scala> emp.limit(1).union(emp.limit(1)).show
+----+------+-----+-----+----------+-----+----+-----+
|comm|deptno|empno|ename|  hiredate|  job| mgr|  sal|
+----+------+-----+-----+----------+-----+----+-----+
|null|    20| 7369|SMITH|1980-12-17|CLERK|7902|800.0|
|null|    20| 7369|SMITH|1980-12-17|CLERK|7902|800.0|
+----+------+-----+-----+----------+-----+----+-----+

join

scala> emp.join(emp,"ename").show
+------+----+------+-----+----------+---------+----+------+----+------+-----+----------+---------+----+------+
| ename|comm|deptno|empno|  hiredate|      job| mgr|   sal|comm|deptno|empno|  hiredate|      job| mgr|   sal|
+------+----+------+-----+----------+---------+----+------+----+------+-----+----------+---------+----+------+
| SMITH|null|    20| 7369|1980-12-17|    CLERK|7902| 800.0|null|    20| 7369|1980-12-17|    CLERK|7902| 800.0|
| ALLEN| 300|    30| 7499|1981-02-20| SALESMAN|7698|1600.5| 300|    30| 7499|1981-02-20| SALESMAN|7698|1600.5|
|  WARD| 500|    30| 7521|1981-02-22| SALESMAN|7698|1250.0| 500|    30| 7521|1981-02-22| SALESMAN|7698|1250.0|
| JONES|null|    20| 7566|1981-04-02|  MANAGER|7839|2975.0|null|    20| 7566|1981-04-02|  MANAGER|7839|2975.0|
|MARTIN|1400|    30| 7654|1981-09-28| SALESMAN|7698|1250.0|1400|    30| 7654|1981-09-28| SALESMAN|7698|1250.0|
| BLAKE|null|    30| 7698|1981-05-01|  MANAGER|7839|2850.0|null|    30| 7698|1981-05-01|  MANAGER|7839|2850.0|
| CLARK|null|    10| 7782|1981-06-09|  MANAGER|7839|2450.0|null|    10| 7782|1981-06-09|  MANAGER|7839|2450.0|
| SCOTT|null|    20| 7788|1987-04-19|  ANALYST|7566|3000.0|null|    20| 
+------+----+------+-----+----------+---------+----+------+----+------+-----+----------+---------+----+------+

用多个字段连接.不一定要包装在Seq中,List,Array也可以

scala> emp.join(emp,Seq("ename","deptno")).show
+------+------+----+-----+----------+---------+----+------+----+-----+----------+---------+----+------+
| ename|deptno|comm|empno|  hiredate|      job| mgr|   sal|comm|empno|  hiredate|      job| mgr|   sal|
+------+------+----+-----+----------+---------+----+------+----+-----+----------+---------+----+------+
| SMITH|    20|null| 7369|1980-12-17|    CLERK|7902| 800.0|null| 7369|1980-12-17|    CLERK|7902| 800.0|
| ALLEN|    30| 300| 7499|1981-02-20| SALESMAN|7698|1600.5| 300| 7499|1981-02-20| SALESMAN|7698|1600.5|
|  WARD|    30| 500| 7521|1981-02-22| SALESMAN|7698|1250.0| 500| 7521|1981-02-22| SALESMAN|7698|1250.0|
| JONES|    20|null| 7566|1981-04-02|  MANAGER|7839|2975.0|null| 7566|1981-04-02|  MANAGER|7839|2975.0|
|MARTIN|    30|1400| 7654|1981-09-28| SALESMAN|7698|1250.0|1400| 7654|1981-09-28| SALESMAN|7698|1250.0|
| BLAKE|    30|null| 7698|1981-05-01|  MANAGER|7839|2850.0|null| 
+------+------+----+-----+----------+---------+----+------+----+-----+----------+---------+----+------+

内连接

scala> emp.join(emp,Array("ename","deptno"),"inner").show(3)
+-----+------+----+-----+----------+--------+----+------+----+-----+----------+--------+----+------+
|ename|deptno|comm|empno|  hiredate|     job| mgr|   sal|comm|empno|  hiredate|     job| mgr|   sal|
+-----+------+----+-----+----------+--------+----+------+----+-----+----------+--------+----+------+
|SMITH|    20|null| 7369|1980-12-17|   CLERK|7902| 800.0|null| 7369|1980-12-17|   CLERK|7902| 800.0|
|ALLEN|    30| 300| 7499|1981-02-20|SALESMAN|7698|1600.5| 300| 7499|1981-02-20|SALESMAN|7698|1600.5|
| WARD|    30| 500| 7521|1981-02-22|SALESMAN|7698|1250.0| 500| 7521|1981-02-22|SALESMAN|7698|1250.0|
+-----+------+----+-----+----------+--------+----+------+----+-----+----------+--------+----+------+

换种写法

scala> emp.join(emp,emp("ename")===emp("ename")).show(3)
20/11/24 21:27:48 WARN sql.Column: Constructing trivially true equals predicate, 'ename#79 = ename#79'. Perhaps you need to use aliases.
+----+------+-----+-----+----------+--------+----+------+----+------+-----+-----+----------+--------+----+------+
|comm|deptno|empno|ename|  hiredate|     job| mgr|   sal|comm|deptno|empno|ename|  hiredate|     job| mgr|   sal|
+----+------+-----+-----+----------+--------+----+------+----+------+-----+-----+----------+--------+----+------+
|null|    20| 7369|SMITH|1980-12-17|   CLERK|7902| 800.0|null|    20| 7369|SMITH|1980-12-17|   CLERK|7902| 800.0|
| 300|    30| 7499|ALLEN|1981-02-20|SALESMAN|7698|1600.5| 300|    30| 7499|ALLEN|1981-02-20|SALESMAN|7698|1600.5|
| 500|    30| 7521| WARD|1981-02-22|SALESMAN|7698|1250.0| 500|    30| 7521| WARD|1981-02-22|SALESMAN|7698|1250.0|
+----+------+-----+-----+----------+--------+----+------+----+------+-----+-----+----------+--------+----+------+

求交集

scala> emp.intersect(emp.limit(1)).show()
+----+------+-----+-----+----------+-----+----+-----+
|comm|deptno|empno|ename|  hiredate|  job| mgr|  sal|
+----+------+-----+-----+----------+-----+----+-----+
|null|    20| 7369|SMITH|1980-12-17|CLERK|7902|800.0|
+----+------+-----+-----+----------+-----+----+-----+

一个有而另一个没有

scala> emp.except(emp.limit(1)).show()
+----+------+-----+------+----------+---------+----+------+
|comm|deptno|empno| ename|  hiredate|      job| mgr|   sal|
+----+------+-----+------+----------+---------+----+------+
|null|    30| 7900| JAMES|1981-12-03|    CLERK|7698| 950.0|
|null|    20| 7902|  FORD|1981-12-02|  ANALYST|7566|3000.0|
|1400|    30| 7654|MARTIN|1981-09-28| SALESMAN|7698|1250.0|
| 300|    30| 7499| ALLEN|1981-02-20| SALESMAN|7698|1600.5|
|null|    10| 7782| CLARK|1981-06-09|  MANAGER|7839|2450.0|
|null|    20| 7788| SCOTT|1987-04-19|  ANALYST|7566|3000.0|
| 500|    30| 7521|  WARD|1981-02-22| SALESMAN|7698|1250.0|
|null|    10| 7934|MILLER|1982-01-23|    CLERK|7369|1300.0|
|null|    10| 7839|  KING|1981-11-17|PRESIDENT|null|5000.0|
|   0|    30| 7844|TURNER|1981-09-08| SALESMAN|7698|1500.0|
|null|    30| 7698| BLAKE|1981-05-01|  MANAGER|7839|2850.0|
|null|    20| 7566| JONES|1981-04-02|  MANAGER|7839|2975.0|
|null|    20| 7876| ADAMS|1987-05-23|    CLERK|7788|1100.0|
+----+------+-----+------+----------+---------+----+------+

重命名

scala> emp.withColumnRenamed("deptno",  "no").show(3)
+----+---+-----+-----+----------+--------+----+------+
|comm| no|empno|ename|  hiredate|     job| mgr|   sal|
+----+---+-----+-----+----------+--------+----+------+
|null| 20| 7369|SMITH|1980-12-17|   CLERK|7902| 800.0|
| 300| 30| 7499|ALLEN|1981-02-20|SALESMAN|7698|1600.5|
| 500| 30| 7521| WARD|1981-02-22|SALESMAN|7698|1250.0|
+----+---+-----+-----+----------+--------+----+------+

新增1列

scala> emp.withColumn("salary2",emp("sal")).show(2)
+----+------+-----+-----+----------+--------+----+------+-------+
|comm|deptno|empno|ename|  hiredate|     job| mgr|   sal|salary2|
+----+------+-----+-----+----------+--------+----+------+-------+
|null|    20| 7369|SMITH|1980-12-17|   CLERK|7902| 800.0|  800.0|
| 300|    30| 7499|ALLEN|1981-02-20|SALESMAN|7698|1600.5| 1600.5|
+----+------+-----+-----+----------+--------+----+------+-------+

两种风格的写法

DSL风格写法

//降序排列
val df1: Unit = df.select("empno","ename","deptno")
    .orderBy($"deptno".desc).show()

SQL风格写法

 df.createTempView("emp")
  val sql="select * from emp where deptno<30"
  private val frame: DataFrame = spark.sql(sql.stripMargin)
  frame.show()
鸭梨山大哎
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Spark-SQL常用聚合函数.md
08-09
Spark-SQL常用聚合函数.md
spark sql 和常用方法汇总
sslfk的博客
12-07 229
spark sql 学习： def main(args: Array[String]): Unit = { val sparksession = SparkSession.builder().appName("HBaseTest").master("local").getOrCreate() val sc = sparksession.sparkContext //设置日...
SparkSql中的方法
qq_41769288的博客
10-31 208
1,去掉空值 df.na.fill() 如果想把空值全部替换为"" ,那么只需在方法中填写"" 如果想把某些字段为空的替换,那么需填写一个map, Map("字段"-&gt;替换后的值,,,,,), map中可以存放多个字段与替换后的值的映射...
Spark框架——SparkSQL的运用及方法
那人独钓寒江雪
03-30 618
package sql import org.apache.avro.ipc.specific.Person import org.apache.spark import org.apache.spark.rdd.RDD import org.apache.spark.sql import org.apache.spark.sql.catalyst.InternalRow import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession.
Spark的基本结构及SparkSQL组件的基本用法
公众号：瑞行AI
04-27 2603
（1）有关sbt的理解参见： https://www.scala-sbt.org/0.13/docs/zh-cn/Getting-Started.html （2）基于Intellij IDEA的Scala环境搭建和学习教程，参考易百教程： https://www.yiibai.com/scala/ （3）Scala API文档（查看可用类型和方法）： https://www.scala-l
Spark-SQL教程
qq_41878423的博客
11-28 4492
目录创建SparkSession通过SparkSession创建SparkContext手动创建DataFrame创建DataSetDataSet的使用 - 读取文本文件DataSet的使用 - 读取MySQLSpark_On_Hivestandalone模式hive集群模式搭建hive-metastore服务的配置hiveserver2服务的配置hive 的命令行简单介绍IDEA使用spark程序交互hiveSpark-Sql服务SPARK整合HIVESpark-Sqlspark-sql函数自定义函数
Spark SQL常见4种数据源详解
01-19
通用load/write方法 手动指定选项 Spark SQL的DataFrame接口支持多种数据源的操作。一个DataFrame可以进行RDDs方式的操作，也可以被注册为临时表。把DataFrame注册为临时表之后，就可以对该DataFrame执行SQL查询。...
spark-sql等等
04-28
本文详细的描述了spark-sql的一些常用的命令等。。。。。
基于hbase + spark 实现常用推荐算法（主要用于精准广告投放和推荐系统）.zip
08-03
通过学习Spark，我了解了其基本概念和核心组件，如RDD（弹性分布式数据集）、Spark SQL、Spark Streaming和MLlib等。我学会了使用Spark编写分布式的数据处理程序，并通过调优技巧提高了程序的性能。在实践过程中，我...
如何限制spark任务占用yarn资源的最大内存和cpu
linweidong的专栏
05-15 248
请注意，设置资源限制时需要考虑作业的实际需求，以避免资源不足导致作业执行失败。同时，您可能需要与集群管理员协商，以确保作业的资源请求不会超过集群的容量。参数允许您设置非常多的 Spark 配置选项，包括与资源相关的。您可以通过查看 Spark 的官方文档来了解更多关于这些配置的详细信息。提交 PySpark 作业时，可以通过设置一些参数来限制任务占用的 YARN 资源，包括内存和CPU。为了限制作业的最大资源使用，您可以调整上述参数。
Spark RDD案例：统计网站每月访问量
howard2005的专栏
05-15 1052
这个项目利用Spark技术，通过统计网站访问记录中的日期信息，实现了对每月访问量的统计和排序。通过分析数据，我们可以了解到不同月份的网站访问情况，为进一步优化网站内容和推广策略提供数据支持。
【经验03】spark处理离线数据速度缓慢遇到的坑
奋斗的阿杰的博客
05-14 70
03-spark处理离线数据速度缓慢遇到的坑
数据库SQL编写规范-SQL书写规范整理（SQL语句书写规范全解-Word原件）
xx_123321456的博客
05-14 306
​​​​​​工作安排任务书，可行性分析报告，立项申请审批表，产品需求规格说明书，需求调研计划，用户需求调查单，用户需求说明书，概要设计说明书，技术解决方案，数据库设计说明书，详细设计说明书，单元测试报告，总体测试计划，单元测试计划，产品集成计划，集成测试报告，集成测试计划，系统测试报告，产品交接验收单，验收报告，验收测试报告，压力测试报告，项目总结报告，立项结项审批表，成本估算表，项目计划，项目周报月报，风险管理计划，质量保证措施，项目甘特图，项目管理工具，操作手册，接口设计文档，软件实施方案，运维方案，安
1、sql server数据库进行sql注入
weixin_51520483的博客
05-15 826
1、判断数据类型是很重要的一点，影响后面的注入语句2、确定列数时，要多测几组数据，从而分析判断列数，并不是遇到第一个报错就停止测试3、正常网页（例如id=1，参数正确）没有注入回显时，试试报错情况（id=-1），注入回显有可能被正常返回数据覆盖4、注入回显的字段有可能整形、字符、小数等，注意是否需要单引号5、最好先全部统一会先字段类型，比如全部整形，挨个轮流改变为字符形。
使用DBeaver的第2天-使用sql导入数据
2301_78094435的博客
05-14 249
但是如果你执行失败了，多半可能是因为本地没有部署mysql，记住只有本地有mysql才可以执行脚本，否则你只能粘贴脚本，到sql控制台执行了。特别注意，较长的脚本粘贴的话一定会闪退，所以只有安装mysql在本地才能解决这个问题；首先位置一定要放在库上（实例），放在表上可不好使用哦。使用sql导入数据这块我会仔细的说一下。然后点击工具-再点击执行脚本。
小白也会SQL：大模型改变交互方式（中）
鲁班模锤
05-17 749
在人工智能与自然语言处理交汇点，有一种技术正悄然改变与数据交互的方式——将日常语言转化为精准SQL查询。这一“text-to-sql”转换任务，使非专业人士也能轻松驾驭复杂的数据库操作，极大地拓宽了数据应用的边界。
idea创建springboot项目报错:Initialization failed for ‘https://start.spring.io‘
最新发布
爱吃Java的羊的博客
05-18 129
>勾选Automatic proxy configuration URL: 复选框，并在输入框中录入。->勾选Auto-detect proxy settings复选框。->点击Appearance & Behavior菜单。idea创建springboot项目报错。->点击HTTP Proxy菜单。打开Settings界面。->点击OK报错退出。
写SQL的心得
weixin_64842400的博客
05-11 629
接着，系统按照 GROUP BY 子句中的指定字段分组，并对每个分组进行计算，生成虚拟的分组结果表。执行顺序 ：from > on > where > group by > having > select > distinct > order by > top。4、聚合函数本质：对指定的列进行聚合，如果我们用了group by，我们可以对每个分组应用内聚合函数。在分组内部，聚合函数会自动处理所有重复的行。具体来说，在执行 SELECT 子句时，系统先计算 SELECT 中的列表达式和函数等，然后。
PostgreSQL的pg_top工具
lee_vincent1的博客
05-16 420
pg_top是一个用于 PostgreSQL 数据库的实时监控和性能分析工具，类似于 Unix 系统中的top命令。它显示了 PostgreSQL 的活动会话、SQL 查询、锁定信息、缓存命中率、I/O 操作等信息，可以帮助数据库管理员快速识别和诊断性能问题。pg_top。
spark sql常用函数
04-20
以下是一些常用的Spark SQL函数： 1. 聚合函数： - count：计算非空值的数量。 - sum：计算数值列的总和。 - avg：计算数值列的平均值。 - max：计算数值列的最大值。 - min：计算数值列的最小值。 2. 字符串...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值