hive python spark_Spark实战(六)spark SQL + hive(Python版)

一、hive环境准备

1、安装hive

按照hive安装步骤安装好hive

CREATE USER 'spark'@'%' IDENTIFIED BY '123456';

GRANT all privileges ON hive.* TO 'spark'@'%';

1

2

flush privileges;

2、环境配置

将配置好的hive-site.xml放入$SPARK-HOME/conf目录下,,下载mysql连接驱动，并拷贝至spark/lib目录下

启动spark-shell时指定mysql连接驱动位置

bin/spark-shell --master spark://mini1:7077 --executor-memory 1g --total-executor-cores 2 --driver-class-path /home/hadoop/spark/lib/mysql-connector-java-5.1.35-bin.jar

1

二、Spark SQL

1、概述

Spark SQL是Spark用来处理结构化数据的一个模块，它提供了一个编程抽象叫做DataFrame并且作为分布式SQL查询引擎的作用。

2、DataFrames

与RDD类似，DataFrame也是一个分布式数据容器。然而DataFrame更像传统数据库的二维表格，除了数据以外，还记录数据的结构信息，即schema。同时，与Hive类似，DataFrame也支持嵌套数据类型(struct、array和map)。从API易用性的角度上 看，DataFrame API提供的是一套高层的关系操作，比函数式的RDD API要更加友好，门槛更低。由于与R和Pandas的DataFrame类似，Spark DataFrame很好地继承了传统单机数据分析的开发体验。

3、操作实例

from pyspark import Row

from pyspark.sql import SparkSession

from pyspark.sql.types import StructField, StringType, StructType

if __name__ == "__main__":

spark = SparkSession\

.builder\

.appName("PythonWordCount")\

.master("local")\

.getOrCreate()

sc = spark.sparkContext

line = sc.textFile("D:\\code\\hadoop\\data\\spark\\day4\\person.txt").map(lambda x: x.split(' '))

# personRdd = line.map(lambda p: Row(id=p[0], name=p[1], age=int(p[2])))

# personRdd_tmp = spark.createDataFrame(personRdd)

# personRdd_tmp.show()

#读取数据

schemaString = "id name age"

fields = list(map(lambda fieldName: StructField(fieldName, StringType(), nullable=True), schemaString.split(" ")))

schema = StructType(fields)

rowRDD = line.map(lambda attributes: Row(attributes[0], attributes[1],attributes[2]))

peopleDF = spark.createDataFrame(rowRDD, schema)

peopleDF.createOrReplaceTempView("people")

results = spark.sql("SELECT * FROM people")

results.rdd.map(lambda attributes: "name: " + attributes[0] + "," + "age:" + attributes[1]).foreach(print)

# SQL风格语法

# personRdd_tmp.registerTempTable("person")

# spark.sql("select * from person where age >= 20 order by age desc limit 2").show()

#方法风格语法

# personRdd_tmp.select("name").show()

# personRdd_tmp.select(personRdd_tmp['name'], personRdd_tmp['age'] + 1).show()

# personRdd_tmp.filter(personRdd_tmp['age'] > 21).show()

# personRdd_tmp.groupBy("age").count().show()

# personRdd_tmp.createOrReplaceTempView("people")

# sqlDF = spark.sql("SELECT * FROM people")

# sqlDF.show()

# personRdd_tmp.createGlobalTempView("people")

# spark.sql("SELECT * FROM global_temp.people").show()

#

# spark.newSession().sql("SELECT * FROM global_temp.people").show()

# 保存为指定格式

# people = line.map(lambda p: (p[0],p[1], p[2].strip()))

# schemaString = "id name age"

#

# fields = [StructField(field_name, StringType(), True) for field_name in schemaString.split()]

# # # 通过StructType直接指定每个字段的schema

# schema = StructType(fields)

# schemaPeople = spark.createDataFrame(people, schema)

# schemaPeople.createOrReplaceTempView("people")

# results = spark.sql("SELECT * FROM people")

# results.write.json("D:\\code\\hadoop\\data\\spark\\day4\\personout.txt")

# results.write.save("D:\\code\\hadoop\\data\\spark\\day4\\personout1")

# results.show()

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

结果如下：

保存为JSON文件如下

从mysql中读写数据

#JDBC

# sqlContext = SQLContext(sc)

# df = sqlContext.read.format("jdbc").options(url="jdbc:mysql://localhost:3306/hellospark",

# driver="com.mysql.jdbc.Driver", dbtable="(select * from actor) tmp",

# user="root", password="123456").load()

# schemaPeople.write \

# .format("jdbc") \

# .option("url", "jdbc:mysql://localhost:3306/hellospark")\

# .option("dbtable", "person")\

# .option("user", "root")\

# .option("password", "123456")\

# .mode("append")\

# .save()

1

2

3

4

5

6

7

8

9

10

11

12

13

14

三、Spark Sql + Hive

需要指定warehouse_location，并将配置好的hive-site.xml放入$SPARK-HOME/conf目录下

from os.path import abspath

from pyspark.sql import SparkSession

# from pyspark.sql.types import StructField, StringType, StructType

if __name__ == "__main__":

# spark = SparkSession\

# .builder\

# .appName("PythonWordCount")\

# .master("spark://mini1:7077")\

# .getOrCreate()

# sc = spark.sparkContext

warehouse_location = abspath('spark-warehouse')

spark = SparkSession \

.builder \

.appName("Python Spark SQL Hive integration example") \

.config("spark.sql.warehouse.dir", warehouse_location) \

.enableHiveSupport() \

.getOrCreate()

# spark.sql("CREATE TABLE IF NOT EXISTS person(id STRING, name STRING,age STRING) row format delimited fields terminated by ' '")

# spark.sql("LOAD DATA INPATH 'hdfs://mini1:9000/person/data/person.txt' INTO TABLE person")

spark.sql("SELECT * FROM person").show()

# line = sc.textFile("hdfs://mini1:9000/person/data/person.txt").map(lambda x: x.split(' '))

# people = line.map(lambda p: (p[0],p[1], p[2].strip()))

# schemaString = "id name age"

#

# fields = [StructField(field_name, StringType(), True) for field_name in schemaString.split()]

# # # 通过StructType直接指定每个字段的schema

# schema = StructType(fields)

# schemaPeople = spark.createDataFrame(people, schema)

# schemaPeople.createOrReplaceTempView("people")

#

# schemaPeople.write \

# .format("jdbc") \

# .option("url", "jdbc:mysql://192.168.62.132:3306/hellospark")\

# .option("dbtable", "person")\

# .option("user", "root")\

# .option("password", "123456you")\

# .mode("append")\

# .save()

spark.stop()

# sc.stop()

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

还可以使用org.apache.spark.sql.hive.HiveContext来实现。