JavaSparkContext sc // An existing SparkContext.
SQLContext sqlContext = new org.apache.spark.sql.SQLContext(sc)
// Create the DataFrame DataFrame df = sqlContext.read().json(“examples/src/main/resources/people.json”);
// Show the content of the DataFrame df.show(); // age name // null Michael // 30 Andy // 19 Justin
// Print the schema in a tree format df.printSchema(); // root // |-- age: long (nullable = true) // |-- name: string (nullable = true)
// Select only the “name” column df.select(“name”).show(); // name // Michael // Andy // Justin
// Select everybody, but increment the age by 1 df.select(df.col(“name”), df.col(“age”).plus(1)).show(); // name (age + 1) // Michael null // Andy 31 // Justin 20
// Select people older than 21 df.filter(df.col(“age”).gt(21)).show(); // age name // 30 Andy
// Count people by age df.groupBy(“age”).count().show(); // age count // null 1 // 19 1 // 30 1
// Encoders for most common types are automatically provided by importing sqlContext.implicits._
val ds = Seq(1, 2, 3).toDS() ds.map(_ + 1).collect() // Returns: Array(2, 3, 4)
// Encoders are also created for case classes. case class Person(name: String, age: Long) val ds = Seq(Person(“Andy”, 32)).toDS()
// DataFrames can be converted to a Dataset by providing a class. Mapping will be done by name. val path = “examples/src/main/resources/people.json” val people = sqlContext.read.json(path).as[Person]
// sc is an existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc) // this is used to implicitly convert an RDD to a DataFrame. import sqlContext.implicits._
// Define the schema using a case class. // Note: Case classes in Scala 2.10 can support only up to 22 fields. To work around this limit, // you can use custom classes that implement the Product interface. case class Person(name: String, age: Int)
// Create an RDD of Person objects and register it as a table. val people = sc.textFile(“examples/src/main/resources/people.txt”).map(_.split(",")).map(p => Person(p(0), p(1).trim.toInt)).toDF() people.registerTempTable(“people”)
// SQL statements can be run by using the sql methods provided by sqlContext. val teenagers = sqlContext.sql(“SELECT name, age FROM people WHERE age >= 13 AND age <= 19”)
// The results of SQL queries are DataFrames and support all the normal RDD operations. // The columns of a row in the result can be accessed by field index: teenagers.map(t => "Name: " + t(0)).collect().foreach(println)
// or by field name: teenagers.map(t => "Name: " + t.getAs[String](“name”)).collect().foreach(println)
// row.getValuesMap[T] retrieves multiple columns at once into a Map[String, T] teenagers.map(_.getValuesMap[Any](List(“name”, “age”))).collect().foreach(println) // Map(“name” -> “Justin”, “age” -> 19)
编程指定schema
当case class不能提前定义好时,可以通过以下三步通过代码创建DataFrame
将RDD转为包含row对象的RDD
基于structType类型创建schema,与第一步创建的RDD相匹配
通过SQLContext的createDataFrame方法对第一步的RDD应用schema
// sc is an existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
// Create an RDD val people = sc.textFile(“examples/src/main/resources/people.txt”)
// The schema is encoded in a string val schemaString = “name age”
// Import Row. import org.apache.spark.sql.Row;
// Import Spark SQL data types import org.apache.spark.sql.types.{StructType,StructField,StringType};
// Generate the schema based on the string of schema val schema = StructType( schemaString.split(" ").map(fieldName => StructField(fieldName, StringType, true)))
// Convert records of the RDD (people) to Rows. val rowRDD = people.map(_.split(",")).map(p => Row(p(0), p(1).trim))
// Apply the schema to the RDD. val peopleDataFrame = sqlContext.createDataFrame(rowRDD, schema)
// Register the DataFrames as a table. peopleDataFrame.registerTempTable(“people”)
// SQL statements can be run by using the sql methods provided by sqlContext. val results = sqlContext.sql(“SELECT name FROM people”)
// The results of SQL queries are DataFrames and support all the normal RDD operations. // The columns of a row in the result can be accessed by field index or by field name. results.map(t => "Name: " + t(0)).collect().foreach(println)
// sqlContext from the previous example is used in this example.
// This is used to implicitly convert an RDD to a DataFrame.
import sqlContext.implicits._
val people: RDD[Person] = … // An RDD of case class objects, from the previous example.
// The RDD is implicitly converted to a DataFrame by implicits, allowing it to be stored using Parquet. people.write.parquet(“people.parquet”)
// Read in the parquet file created above. Parquet files are self-describing so the schema is preserved. // The result of loading a Parquet file is also a DataFrame. val parquetFile = sqlContext.read.parquet(“people.parquet”)
//Parquet files can also be registered as tables and then used in SQL statements. parquetFile.registerTempTable(“parquetFile”) val teenagers = sqlContext.sql(“SELECT name FROM parquetFile WHERE age >= 13 AND age <= 19”) teenagers.map(t => "Name: " + t(0)).collect().foreach(println)
// sqlContext from the previous example is used in this example.
// This is used to implicitly convert an RDD to a DataFrame.
import sqlContext.implicits._
// Create a simple DataFrame, stored into a partition directory val df1 = sc.makeRDD(1 to 5).map(i => (i, i * 2)).toDF(“single”, “double”) df1.write.parquet(“data/test_table/key=1”)
// Create another DataFrame in a new partition directory, // adding a new column and dropping an existing column val df2 = sc.makeRDD(6 to 10).map(i => (i, i * 3)).toDF(“single”, “triple”) df2.write.parquet(“data/test_table/key=2”)
// Read the partitioned table val df3 = sqlContext.read.option(“mergeSchema”, “true”).parquet(“data/test_table”) df3.printSchema()
// The final schema consists of all 3 columns in the Parquet files together // with the partitioning column appeared in the partition directory paths. // root // |-- single: int (nullable = true) // |-- double: int (nullable = true) // |-- triple: int (nullable = true) // |-- key : int (nullable = true)
// sqlContext is an existing HiveContext
sqlContext.refreshTable("my_table")
配置
与parquet相关的配置参数如下所示
参数
默认值
描述
spark.sql.parquet.binaryAsString
false
该选项让SparkSQL将string安装二进制数据按照字符串处理,以便兼容老系统
spark.sql.parquet.int96AsTimestamp
true
Some Parquet-producing systems, in particular Impala and Hive, store Timestamp into INT96. This flag tells Spark SQL to interpret INT96 data as a timestamp to provide compatibility with these systems.
The output committer class used by Parquet. The specified class needs to be a subclass of org.apache.hadoop.mapreduce.OutputCommitter. Typically, it's also a subclass of org.apache.parquet.hadoop.ParquetOutputCommitter.
// sc is an existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
// A JSON dataset is pointed to by path. // The path can be either a single text file or a directory storing text files. val path = “examples/src/main/resources/people.json” val people = sqlContext.read.json(path)
// The inferred schema can be visualized using the printSchema() method. people.printSchema() // root // |-- age: integer (nullable = true) // |-- name: string (nullable = true)
// Register this DataFrame as a table. people.registerTempTable(“people”)
// SQL statements can be run by using the sql methods provided by sqlContext. val teenagers = sqlContext.sql(“SELECT name FROM people WHERE age >= 13 AND age <= 19”)
// Alternatively, a DataFrame can be created for a JSON dataset represented by // an RDD[String] storing one JSON object per string. val anotherPeopleRDD = sc.parallelize( “”"{“name”:“Yin”,“address”:{“city”:“Columbus”,“state”:“Ohio”}}""" :: Nil) val anotherPeople = sqlContext.read.json(anotherPeopleRDD)
// sc is an existing SparkContext.
val sqlContext = new org.apache.spark.sql.hive.HiveContext(sc)
sqlContext.sql(“CREATE TABLE IF NOT EXISTS src (key INT, value STRING)”) sqlContext.sql(“LOAD DATA LOCAL INPATH ‘examples/src/main/resources/kv1.txt’ INTO TABLE src”)
// Queries are expressed in HiveQL sqlContext.sql(“FROM src SELECT key, value”).collect().foreach(println)
hive.server2.transport.mode - Set this to value: http hive.server2.thrift.http.port - HTTP port number fo listen on; default is 10001 hive.server2.http.endpoint - HTTP endpoint; default is cliservice