08 学生课程分数的Spark SQL分析

[root@localhost ~]# pyspark
Python 3.8.3 (default, May 10 2021, 17:05:21)
[GCC 8.3.1 20191121 (Red Hat 8.3.1-5)] on linux
Type "help", "copyright", "credits" or "license" for more information.
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
Welcome to
____ __
/ __/__ ___ _____/ /__
_\ \/ _ \/ _ `/ __/ '_/
/__ / .__/\_,_/_/ /_/\_\ version 3.1.1
/_/

Using Python version 3.8.3 (default, May 10 2021 17:05:21)
Spark context Web UI available at http://192.168.206.134:4040
Spark context available as 'sc' (master = local[*], app id = local-1622107705407).
SparkSession available as 'spark'.
>>> from pyspark.sql.types import IntegerType,StringType
>>> from pyspark.sql.types import IntegerType,StringType,S
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ImportError: cannot import name 'S' from 'pyspark.sql.types' (/usr/local/spark/spark-3.1.1-bin-hadoop2.7/python/pyspark/sql/types.py)
>>> from pyspark.sql.types import IntegerType,StringType,StringType,StructField,structType
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ImportError: cannot import name 'structType' from 'pyspark.sql.types' (/usr/local/spark/spark-3.1.1-bin-hadoop2.7/python/pyspark/sql/types.py)
>>> from pyspark.sql.types import IntegerType,StringType,StringType,StructField,StructType
>>> rdd=sc.textFile( "file:///wenjian/chapter4-data01.txt").map(lambda s:s.split(','))
>>> rdd.first()
['Aaron', 'OperatingSystem', '100']
>>> fields=[St]
StopAsyncIteration( StringType( StructType(
StopIteration( StructField(
>>> fields=[St]
StopAsyncIteration( StringType( StructType(
StopIteration( StructField(
>>> fields=[St]
StopAsyncIteration( StringType( StructType(
StopIteration( StructField(
>>> fields=[StructField('name',StringType(),True),StructField('course',StringType(),True),StructField('grade',IntegerType(),True)]
>>> schema=StructType( fields )
>>> from spark.sql import Row
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'spark'
>>> from pyspark.sql import Row
>>> data=rdd.map(lambda p:Row(p[0],p[1],int(p[2])))
>>> stu=spark.createDataFrame( data,schema )
>>> stu.select('grade',stu.grade+5).show()
+-----+-----------+
|grade|(grade + 5)|
+-----+-----------+
| 100| 105|
| 50| 55|
| 30| 35|
| 94| 99|
| 18| 23|
| 82| 87|
| 76| 81|
| 30| 35|
| 38| 43|
| 38| 43|
| 92| 97|
| 12| 17|
| 78| 83|
| 98| 103|
| 20| 25|
| 98| 103|
| 88| 93|
| 18| 23|
| 70| 75|
| 80| 85|
+-----+-----------+
only showing top 20 rows

>>> //how many students are there in all
File "<stdin>", line 1
//how many students are there in all
^
SyntaxError: invalid syntax
>>> #how many students are there in all
>>> stu.select('name').distinct().count()
265
>>> #How many courses are offered in total
>>> stu.select('sourse').distinct().show()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/spark/spark-3.1.1-bin-hadoop2.7/python/pyspark/sql/dataframe.py", line 1669, in select
jdf = self._jdf.select(self._jcols(*cols))
File "/usr/local/spark/spark-3.1.1-bin-hadoop2.7/python/lib/py4j-0.10.9-src.zip/py4j/java_gateway.py", line 1304, in __call__
File "/usr/local/spark/spark-3.1.1-bin-hadoop2.7/python/pyspark/sql/utils.py", line 117, in deco
raise converted from None
pyspark.sql.utils.AnalysisException: cannot resolve '`sourse`' given input columns: [course, grade, name];
'Project ['sourse]
+- LogicalRDD [name#0, course#1, grade#2], false

>>> stu.groupBy( 'name').count().show()
+-----------+-----+
| name|count|
+-----------+-----+
| Chad| 6|
| Rod| 4|
| Baron| 6|
| Len| 5|
| Scott| 3|
| Donahue| 5|
| Gerald| 4|
| Monroe| 3|
| Boyd| 3|
| Alvin| 5|
| Elliot| 3|
| Blithe| 3|
| Willie| 4|
| Brady| 5|
|Bartholomew| 5|
| Sidney| 5|
| Benjamin| 4|
| Bruno| 5|
| Clare| 4|
| Herbert| 3|
+-----------+-----+
only showing top 20 rows

>>> stu.groupBy( 'course').count().show()
+---------------+-----+
| course|count|
+---------------+-----+
|ComputerNetwork| 142|
| CLanguage| 128|
| DataStructure| 131|
| DataBase| 126|
| Algorithm| 144|
|OperatingSystem| 134|
| Software| 132|
| Python| 136|
+---------------+-----+

>>> stu.select(stu.grade>95).count()
1073
>>> spark.sql('select * from students where grade>95').show()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/spark/spark-3.1.1-bin-hadoop2.7/python/pyspark/sql/session.py", line 723, in sql
return DataFrame(self._jsparkSession.sql(sqlQuery), self._wrapped)
File "/usr/local/spark/spark-3.1.1-bin-hadoop2.7/python/lib/py4j-0.10.9-src.zip/py4j/java_gateway.py", line 1304, in __call__
File "/usr/local/spark/spark-3.1.1-bin-hadoop2.7/python/pyspark/sql/utils.py", line 117, in deco
raise converted from None
pyspark.sql.utils.AnalysisException: Table or view not found: students; line 1 pos 14;
'Project [*]
+- 'Filter ('grade > 95)
+- 'UnresolvedRelation [students], [], false

>>> spark.sql('select count(name) from students where name="Tom"').show()
+-----------+
|count(name)|
+-----------+
| 5|
+-----------+

>>> spark.sql('select * from students where name="Tom"').show()
+----+---------------+-----+
|name| course|grade|
+----+---------------+-----+
| Tom| DataBase| 26|
| Tom| Algorithm| 12|
| Tom|OperatingSystem| 16|
| Tom| Python| 40|
| Tom| Software| 60|
+----+---------------+-----+

>>> spark.sql('select * from students where name="Tom" order by grade desc').show()
+----+---------------+-----+
|name| course|grade|
+----+---------------+-----+
| Tom| Software| 60|
| Tom| Python| 40|
| Tom| DataBase| 26|
| Tom|OperatingSystem| 16|
| Tom| Algorithm| 12|
+----+---------------+-----+

>>> spark.sql('select avg(grade) from students where name="Tom"').show()
+----------+
|avg(grade)|
+----------+
| 30.8|
+----------+

>>> spark.sql('select course,avg(grade),max(grade),min(grade) from students group by course').show()
+---------------+------------------+----------+----------+
| course| avg(grade)|max(grade)|min(grade)|
+---------------+------------------+----------+----------+
|ComputerNetwork|51.901408450704224| 100| 0|
| CLanguage| 50.609375| 100| 0|
| DataStructure| 47.57251908396947| 100| 0|
| DataBase| 50.53968253968254| 100| 0|
| Algorithm|48.833333333333336| 100| 0|
|OperatingSystem|54.940298507462686| 100| 2|
| Software| 50.90909090909091| 100| 0|
| Python| 57.8235294117647| 100| 2|
+---------------+------------------+----------+----------+

>>> spark.sql('select course,count(name),round(avg(grade),2) from students group by course').show()
+---------------+-----------+------------------------------------+
| course|count(name)|round(avg(CAST(grade AS BIGINT)), 2)|
+---------------+-----------+------------------------------------+
|ComputerNetwork| 142| 51.9|
| CLanguage| 128| 50.61|
| DataStructure| 131| 47.57|
| DataBase| 126| 50.54|
| Algorithm| 144| 48.83|
|OperatingSystem| 134| 54.94|
| Software| 132| 50.91|
| Python| 136| 57.82|
+---------------+-----------+------------------------------------+

posted @ 2021-05-27 19:17 兔子* 阅读(312) 评论(0) 收藏举报

刷新页面返回顶部

兔子*

08 学生课程分数的Spark SQL分析

公告