JAVASparkSQL
1.SparkSQL基础
import java.util.ArrayList;
import java.util.List;
import java.util.Arrays;
import java.util.Collections;
import java.io.Serializable;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.api.java.function.MapFunction;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.Encoder;
import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.RowFactory;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.types.DataTypes;
import org.apache.spark.sql.types.StructField;
import org.apache.spark.sql.types.StructType;
import static org.apache.spark.sql.functions.col;
public class SparkSQL {
public static void main(String[] args) {
SparkSession spark = SparkSession
.builder()
.appName("Java Spark SQL basic example")
.config("spark.some.config.option", "some-value")
.master("local")
.getOrCreate();
// runBaseFrameShow(spark);
// runDatasetCreationExample(spark);
// runInferSchemaExample(spark);
runProgrammaticSchemaExample(spark);
spark.stop();
}
private static void runBaseFrameShow(SparkSession spark){
Dataset<Row> df = spark.read().json("jsonsong.json");
df.show();
// +--------+----+-------------------+------+
// | album|name| path|singer|
// +--------+----+-------------------+------+
// |香港电视剧主题歌| 上海滩|mp3/shanghaitan.mp3| 叶丽仪|
// |香港电视剧主题歌|一生何求|mp3/shanghaitan.mp3| 陈百强|
// | 怀旧专辑| 红日|mp3/shanghaitan.mp3| 李克勤|
// | 怀旧专辑|爱如潮水|mp3/airucaoshun.mp3| 张信哲|
// | 怀旧专辑| 红茶馆| mp3/redteabar.mp3| 陈惠嫻|
// +--------+----+-------------------+------+
df.printSchema(); //打印表结构
// root
// |-- album: string (nullable = true)
// |-- name: string (nullable = true)
// |-- path: string (nullable = true)
// |-- singer: string (nullable = true)
// Select only the "name" column
df.select("name").show();
// +----+
// |name|
// +----+
// | 上海滩|
// |一生何求|
// | 红日|
// |爱如潮水|
// | 红茶馆|
// +----+
// Select everybody, but increment the age by 1
//.plus(1) 加1
df.select(col("album"), col("path")).show(); //选择两行
// +--------+-------------------+
// | album| path|
// +--------+-------------------+
// |香港电视剧主题歌|mp3/shanghaitan.mp3|
// |香港电视剧主题歌|mp3/shanghaitan.mp3|
// | 怀旧专辑|mp3/shanghaitan.mp3|
// | 怀旧专辑|mp3/airucaoshun.mp3|
// | 怀旧专辑| mp3/redteabar.mp3|
// +--------+-------------------+
df.filter(col("album").equalTo("怀旧专辑")).show(); //过滤出只有怀旧专辑的row
// +-----+----+-------------------+------+
// |album|name| path|singer|
// +-----+----+-------------------+------+
// | 怀旧专辑| 红日|mp3/shanghaitan.mp3| 李克勤|
// | 怀旧专辑|爱如潮水|mp3/airucaoshun.mp3| 张信哲|
// | 怀旧专辑| 红茶馆| mp3/redteabar.mp3| 陈惠嫻|
// +-----+----+-------------------+------+
df.groupBy("album").count().show(); //分组计数
// +--------+-----+
// | album|count|
// +--------+-----+
// |香港电视剧主题歌| 2|
// | 怀旧专辑| 3|
// +--------+-----+
df.createOrReplaceTempView("song"); //建立一张临时表，将rdd放入表中
Dataset<Row> sqlDF = spark.sql("SELECT * FROM song where album='怀旧专辑'"); //生成底层的rdd的代码，使用SQL语句查找
sqlDF.show();
// +-----+----+-------------------+------+
// |album|name| path|singer|
// +-----+----+-------------------+------+
// | 怀旧专辑| 红日|mp3/shanghaitan.mp3| 李克勤|
// | 怀旧专辑|爱如潮水|mp3/airucaoshun.mp3| 张信哲|
// | 怀旧专辑| 红茶馆| mp3/redteabar.mp3| 陈惠嫻|
// +-----+----+-------------------+------+
}
private static void runDatasetCreationExample(SparkSession spark) {
Person person = new Person();
person.setName("Andy");
person.setAge(32);
//编码器将对象序列化为字节，person类型的编码器
Encoder<Person> personEncoder = Encoders.bean(Person.class);
Dataset<Person> javaBeanDS = spark.createDataset(
Collections.singletonList(person), //第一个参数为接收的是list类型，将list打包，
personEncoder //创建DataSet类型，最后使用的是person对象
);
javaBeanDS.show(); //这里输出的value值是person对象来的，name和age字段都是从person对象的getter和setter对象中获得的字段进行转换
// +---+----+
// |age|name|
// +---+----+
// | 32|Andy|
// +---+----+
Encoder<Integer> integerEncoder = Encoders.INT(); //integer类型的编码器
Dataset<Integer> primitiveDS = spark.createDataset(Arrays.asList(1, 2, 3), integerEncoder);
Dataset<Integer> transformedDS = primitiveDS.map(new MapFunction<Integer, Integer>() {
@Override
public Integer call(Integer value) throws Exception {
return value + 1;
}
}, integerEncoder);
transformedDS.collect(); // Returns [2, 3, 4]
String path = "people.json";
Dataset<Person> peopleDS = spark.read().json(path).as(personEncoder);
peopleDS.show();
// +----+-------+
// | age| name|
// +----+-------+
// |null|Michael|
// | 30| Andy|
// | 19| Justin|
// +----+-------+
}
private static void runInferSchemaExample(SparkSession spark) {
JavaRDD<Person> peopleRDD = spark.read()
.textFile("people.txt")
.javaRDD() //将DataSet转换为javaRdd
.map(new Function<String, Person>() {
@Override
public Person call(String line) throws Exception {
String[] parts = line.split(",");
Person person = new Person();
person.setName(parts[0]);
person.setAge(Integer.parseInt(parts[1].trim())); //去掉空行。空格
return person;
}
});
//将rdd转换为DataSet，类型为person
Dataset<Row> peopleDF = spark.createDataFrame(peopleRDD, Person.class);
peopleDF.createOrReplaceTempView("people"); //建一张临时表
Dataset<Row> teenagersDF = spark.sql("SELECT name FROM people WHERE age BETWEEN 13 AND 19");
Encoder<String> stringEncoder = Encoders.STRING();
Dataset<String> teenagerNamesByIndexDF = teenagersDF.map(new MapFunction<Row, String>() {
@Override
public String call(Row row) throws Exception {
return "Name: " + row.getString(0); //标号为0的属性，下标
}
}, stringEncoder);
teenagerNamesByIndexDF.show();
// +------------+
// | value|
// +------------+
// |Name: Justin|
// +------------+
// or by field name
Dataset<String> teenagerNamesByFieldDF = teenagersDF.map(new MapFunction<Row, String>() {
@Override
public String call(Row row) throws Exception {
return "Name: " + row.<String>getAs("name"); //获得属性名的值
}
}, stringEncoder);
teenagerNamesByFieldDF.show();
// +------------+
// | value|
// +------------+
// |Name: Justin|
// +------------+
// $example off:schema_inferring$
}
private static void runProgrammaticSchemaExample(SparkSession spark) {
//调用sparkContext方法。
JavaRDD<String> peopleRDD = spark.sparkContext()
.textFile("people.txt", 1)
.toJavaRDD();
//使用代码中的字段设计
String schemaString = "name age";
List<StructField> fields = new ArrayList<>();
for (String fieldName : schemaString.split(" ")) { //创建结构化的字段，传的数组，，数据类型
StructField field = DataTypes.createStructField(fieldName, DataTypes.StringType, true);
fields.add(field);
}
StructType schema = DataTypes.createStructType(fields);
// Convert records of the RDD (people) to Rows
JavaRDD<Row> rowRDD = peopleRDD.map(new Function<String, Row>() {
public Row call(String record) throws Exception {
String[] attributes = record.split(",");
return RowFactory.create(attributes[0], attributes[1].trim());
}
});
// 类型信息也可以来自StructType
Dataset<Row> peopleDataFrame = spark.createDataFrame(rowRDD, schema);
peopleDataFrame.createOrReplaceTempView("people");
Dataset<Row> results = spark.sql("SELECT name FROM people");
Dataset<String> namesDS = results.map(new MapFunction<Row, String>() {
public String call(Row row) throws Exception {
return "Name: " + row.getString(0);
}
}, Encoders.STRING());
namesDS.show();
// +-------------+
// | value|
// +-------------+
// |Name: Michael|
// | Name: Andy|
// | Name: Justin|
// +-------------+
// $example off:programmatic_schema$
}
public static class Person implements Serializable {
private String name;
private int age;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
}
2.sparkSQL查询
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
// $example off:schema_merging$
// $example on:basic_parquet_example$
import org.apache.spark.api.java.function.MapFunction;
import org.apache.spark.sql.Encoders;
// $example on:schema_merging$
// $example on:json_dataset$
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
// $example off:json_dataset$
// $example off:schema_merging$
// $example off:basic_parquet_example$
import org.apache.spark.sql.SparkSession;
public class JavaSQLDataSourceExample {
public static void main(String[] args) {
SparkSession spark = SparkSession
.builder()
.appName("Java Spark SQL data sources example")
.config("spark.some.config.option", "some-value")
.master("local")
.getOrCreate();
// runBasicDataSourceExample(spark);
// runBasicParquetExample(spark);
// runParquetSchemaMergingExample(spark);
runJsonDatasetExample(spark);
// runJdbcDatasetExample(spark);
spark.stop();
}
private static void runBasicDataSourceExample(SparkSession spark) {
// parquet列式存储的文件
Dataset<Row> usersDF = spark.read().load("users.parquet");
usersDF.schema(); //表结构
usersDF.foreach(x->System.out.println(x));
// [Alyssa,null,WrappedArray(3, 9, 15, 20)]
// [Ben,red,WrappedArray()]
//提取两个字段的 信息，单独存取在另一个parquet目录，目录中有个parquet文件
usersDF.select("name", "favorite_color").write().save("parquet/namesAndFavColors.parquet");
Dataset<Row> peopleDF = spark.read().format("json").load("people.json");
//将json格式的数据以parquet格式的文件保存
peopleDF.select("name", "age").write().format("parquet").save("parquet/namesAndAges.parquet");
//此处调用白鸥只需要用parquet当做表就行了，不用放在内存中使用
Dataset<Row> sqlDF = spark.sql("SELECT * FROM parquet.`users.parquet`");
sqlDF.foreach(x->System.out.println(x));
// [Alyssa,null,WrappedArray(3, 9, 15, 20)]
// [Ben,red,WrappedArray()]
Dataset<Row> sqlDF1 = spark.sql("SELECT * FROM json.`people.json`");
sqlDF1.foreach(x->System.out.println(x));
// [null,Michael]
// [30,Andy]
// [19,Justin]
}
private static void runBasicParquetExample(SparkSession spark) {
Dataset<Row> peopleDF = spark.read().json("people.json");
// 将json文件保存为parquet目录文件
peopleDF.write().parquet("parquet/people.parquet");
Dataset<Row> parquetFileDF = spark.read().parquet("people.parquet");
parquetFileDF.createOrReplaceTempView("parquetFile");
Dataset<Row> namesDF = spark.sql("SELECT name FROM parquetFile WHERE age BETWEEN 13 AND 19");
Dataset<String> namesDS = namesDF.map(new MapFunction<Row, String>() {
public String call(Row row) {
return "Name: " + row.getString(0);
}
}, Encoders.STRING());
namesDS.show();
// +------------+
// | value|
// +------------+
// |Name: Justin|
// +------------+
}
private static void runParquetSchemaMergingExample(SparkSession spark) {
List<Square> squares = new ArrayList<>();
for (int value = 1; value <= 5; value++) {
Square square = new Square();
square.setValue(value);
square.setSquare(value * value);
squares.add(square);
}
Dataset<Row> squaresDF = spark.createDataFrame(squares, Square.class);
squaresDF.write().parquet("parquet/data/test_table/key=1"); //以parquet格式保存squre list
List<Cube> cubes = new ArrayList<>();
for (int value = 6; value <= 10; value++) {
Cube cube = new Cube();
cube.setValue(value);
cube.setCube(value * value * value);
cubes.add(cube);
}
Dataset<Row> cubesDF = spark.createDataFrame(cubes, Cube.class);
cubesDF.write().parquet("parquet/data/test_table/key=2");
// 将同一目录下的多个parquet目录文件合并后为1个
Dataset<Row> mergedDF = spark.read().option("mergeSchema", true).parquet("parquet/data/test_table");
mergedDF.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
// |-- value: int (nullable = true)
// |-- square: int (nullable = true)
// |-- cube: int (nullable = true)
// |-- key: int (nullable = true)
mergedDF.foreach(x->System.out.println(x));
// [1,1,null,1]
// [4,2,null,1]
// [9,3,null,1]
// [16,4,null,1]
// [25,5,null,1]
// [null,6,216,2]
// [null,7,343,2]
// [null,8,512,2]
// [null,9,729,2]
// [null,10,1000,2]
}
private static void runJsonDatasetExample(SparkSession spark) {
Dataset<Row> people = spark.read().json("people.json");
people.printSchema();
// root
// |-- age: long (nullable = true)
// |-- name: string (nullable = true)
people.createOrReplaceTempView("people"); //在内存中创建一张临时表
Dataset<Row> namesDF = spark.sql("SELECT name FROM people WHERE age BETWEEN 13 AND 19");
namesDF.show();
// +------+
// | name|
// +------+
// |Justin|
// +------+
}
private static void runJdbcDatasetExample(SparkSession spark) {
Dataset<Row> jdbcDF = spark.read()
.format("jdbc")
.option("url", "jdbc:postgresql:dbserver")
.option("dbtable", "schema.tablename")
.option("user", "username")
.option("password", "password")
.load();
}
public static class Square implements Serializable {
private int value;
private int square;
// Getters and setters...
// $example off:schema_merging$
public int getValue() {
return value;
}
public void setValue(int value) {
this.value = value;
}
public int getSquare() {
return square;
}
public void setSquare(int square) {
this.square = square;
}
// $example on:schema_merging$
}
public static class Cube implements Serializable {
private int value;
private int cube;
// Getters and setters...
// $example off:schema_merging$
public int getValue() {
return value;
}
public void setValue(int value) {
this.value = value;
}
public int getCube() {
return cube;
}
public void setCube(int cube) {
this.cube = cube;
}
// $example on:schema_merging$
}
}
