Parquet文件:是一种流行的列式存储格式,以二进制存储,文件中包含数据与元数据
package nj.zb.kb09.sql
import org.apache.spark.sql.{Row, SparkSession}
import org.apache.spark.sql.types.{IntegerType, StringType, StructField, StructType}
object SparkSQL1 {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().master("local[*]")
.appName("sparksql").getOrCreate()
val sc = spark.sparkContext
//spark sql 写 parquet 文件
val schema = StructType(Array(
StructField("name", StringType, true),
StructField("sex", StringType, true),
StructField("age", IntegerType, true)
))
val rowRDD = sc.parallelize(List(
("Alice", "F", 18), ("Bob", "M", 19), ("Rose", "F", 17)
)).map(x => Row(x._1, x._2, x._3))
val infoDF = spark.createDataFrame(rowRDD,schema)
// infoDF.write.parquet("out/info")
//spark sql 读 parquet 文件
val df = spark.read.parquet("out/info")
df.show()
df.printSchema()
}
}
hive表
首先需要将hive-site.xml拷贝到spark/conf目录下
cp /opt/hive/conf/hive-site.xml /opt/spark/conf
然后将jar包拷贝到spark/jars
cp /opt/hive/lib/mysql-connector.jar /opt/spark/jars
然后启动元数据服务
hive --service metastore &
直到输出jps后出现runJar进程后按ctrl+c
进入hive创建hive表
hive>create table toronto(full_name string, ssn string, office_address string);
hive>insert into toronto(full_name, ssn, office_address) values('John S. ', '111-222-333 ', '123 Yonge Street ');
集成hive后spark-shell可以直接访问hive表
val df=spark.table("toronto")
df.printSchema
df.show
通过idea建maven工程也能达到效果
需要在pom.xml下添加依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.36</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.11</artifactId>
<version>2.1.1</version>
</dependency>
package nj.zb.kb09.sql
import org.apache.spark.sql.SparkSession
object SparkSQL2 {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().master("local[*]")
.appName("sparksqlHive")
.config("hive.metastore.uris","thrift://192.168.153.10:9083")
.enableHiveSupport().getOrCreate()
spark.sql("select * from toronto").show()
}
}
RDBMS(关系型数据库)表
同样需要拷贝jar包到spark/jars目录下
cp /opt/hive/lib/mysql-connector.jar /opt/spark/jars
package nj.zb.kb09.sql
import java.util.Properties
import org.apache.spark.sql.SparkSession
object SparkSQL2 {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().appName("sparksqlMysql")
.master("local[*]").getOrCreate()
val url="jdbc:mysql://192.168.153.10:3306/hive"
//设置mysql连接用户、密码、数据库驱动类
val prop=new Properties()
prop.setProperty("user","root")
prop.setProperty("password","ok")
prop.setProperty("driver","com.mysql.jdbc.Driver")
//获取TBLS表数据
val df = spark.read.jdbc(url,"TBLS",prop)
df.printSchema()
df.show()
}
}