1、创建DataFrame
本文所使用的DataFrame是通过读取mysql数据库获得的,代码如下:
val spark = SparkSession
.builder()
.appName("Spark SQL basic example")
.enableHiveSupport()
//.config("spark.some.config.option", "some-value")
.getOrCreate()
import spark.implicits._
val url = "jdbc:mysql://localhost:3306/test"
val df = spark.read
.format("jdbc")
.option("url", url)
.option("dbtable", "pivot")
.option("user", "root")
.option("password", "admin")
.load()
2、DataFrame基本动作运算
2.1 show展示数据
可以用show() 方法来展示数据,show有以下几种不同的使用方式:
show():显示所有数据
show(n) :显示前n条数据
show(true): 最多显示20个字符,默认为true
show(false): 去除最多显示20个字符的限制
show(n, true):显示前n条并最多显示20个自负
代码为:
df.show()
df.show(3)
df.show(true)
df.show(false)
df.show(3,true)
上面的输出为:
+---+----+----+--------------------+
| id|user|type| visittime|
+---+----+----+--------------------+
| 1| 1| 助手1|2017-08-10 13:44:...|
| 2| 1|APP1|2017-08-04 13:44:...|
| 3| 2| 助手1|2017-08-05 13:44:...|
| 4| 2| 助手1|2017-08-07 13:44:...|
| 5| 3|APP1|2017-08-02 13:44:...|
| 6| 3|APP1|2017-08-01 13:44:...|
| 7| 3| 助手2|2017-08-14 13:44:...|
| 8| 3|APP2|2017-08-03 13:44:...|
| 9| 2|APP2|2017-08-11 13:44:...|
| 10| 2| 助手1|2017-07-14 13:44:...|
| 11| 1|APP1|2017-07-15 13:45:...|
| 12| 1| 助手2|2017-07-07 13:45:...|
+---+----+----+--------------------+
+---+----+----+--------------------+
| id|user|type| visittime|
+---+----+----+--------------------+
| 1| 1| 助手1|2017-08-10 13:44:...|
| 2| 1|APP1|2017-08-04 13:44:...|
| 3| 2| 助手1|2017-08-05 13:44:...|
+---+----+----+--------------------+
only showing top 3 rows
+---+----+----+--------------------+
| id|user|type| visittime|
+---+----+----+--------------------+
| 1| 1| 助手1|2017-08-10 13:44:...|
| 2| 1|APP1|2017-08-04 13:44:...|
| 3| 2| 助手1|2017-08-05 13:44:...|
| 4| 2| 助手1|2017-08-07 13:44:...|
| 5| 3|APP1|2017-08-02 13:44:...|
| 6| 3|APP1|2017-08-01 13:44:...|
| 7| 3| 助手2|2017-08-14 13:44:...|
| 8| 3|APP2|2017-08-03 13:44:...|
| 9| 2|APP2|2017-08-11 13:44:...|
| 10| 2| 助手1|2017-07-14 13:44:...|
| 11| 1|APP1|2017-07-15 13:45:...|
| 12| 1| 助手2|2017-07-07 13:45:...|
+---+----+----+--------------------+
+---+----+----+---------------------+
|id |user|type|visittime |
+---+----+----+---------------------+
|1 |1 |助手1 |2017-08-10 13:44:19.0|
|2 |1 |APP1|2017-08-04 13:44:26.0|
|3 |2 |助手1 |2017-08-05 13:44:29.0|
|4 |2 |助手1 |2017-08-07 13:44:32.0|
|5 |3 |APP1|2017-08-02 13:44:38.0|
|6 |3 |APP1|2017-08-01 13:44:41.0|
|7 |3 |助手2 |2017-08-14 13:44:48.0|
|8 |3 |APP2|2017-08-03 13:44:45.0|
|9 |2 |APP2|2017-08-11 13:44:53.0|
|10 |2 |助手1 |2017-07-14 13:44:57.0|
|11 |1 |APP1|2017-07-15 13:45:03.0|
|12 |1 |助手2 |2017-07-07 13:45:08.0|
+---+----+----+---------------------+
+---+----+----+--------------------+
| id|user|type| visittime|
+---+----+----+--------------------+
| 1| 1| 助手1|2017-08-10 13:44:...|
| 2| 1|APP1|2