SPARK-SQL - join关联的api

准备文件users.json

{"userId":"1", "userName":"Join", "age":23}
{"userId":"3", "userName":"Katy", "age":23}
{"userId":"4", "userName":"Mike", "age":21}
{"userId":"2", "userName":"Jeffy", "age":45}
{"userId":"5", "userName":"Av", "age":43}

准备文件orders.json

{"id":"1", "userId":"1", "userName":"Join", "totalPrice":80.0,"qty":3.0}
{"id":"2", "userId":"1", "userName":"Join", "totalPrice":50.0,"qty":3.0}
{"id":"3", "userId":"2", "userName":"Jeffy", "totalPrice":200.0,"qty":3.0}
{"id":"4", "userId":"99999", "userName":"zombie", "totalPrice":222.0,"qty":3.0}

准备文件order_items.json

{"id":"1", "orderId":"1", "name":"apple", "amount":4, "price":20.0, "userId":"1"}
{"id":"2", "orderId":"2", "name":"book", "amount":5, "price":10.0, "userId":"1"}
{"id":"3", "orderId":"3", "name":"cake", "amount":1, "price":200.0, "userId":"2"}

代码示例

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;public class test_29_1 {public static void main(String[] args) {SparkSession spark = SparkSession.builder().config("spark.driver.host", "localhost").appName("JoinApiTest").master("local").getOrCreate();spark.sparkContext().setLogLevel("ERROR");Dataset<Row> usersDataSet = spark.read().json(Utils.BASE_PATH + "/join/users.json");usersDataSet.show();/*+---+------+--------+|age|userId|userName|+---+------+--------+| 23|     1|    Join|| 23|     3|    Katy|| 21|     4|    Mike|| 45|     2|   Jeffy|| 43|     5|      Av|+---+------+--------+*/Dataset<Row> ordersDataSet = spark.read().json(Utils.BASE_PATH + "/join/orders.json");ordersDataSet.show();/*+---+---+----------+------+--------+| id|qty|totalPrice|userId|userName|+---+---+----------+------+--------+|  1|3.0|      80.0|     1|    Join||  2|3.0|      50.0|     1|    Join||  3|3.0|     200.0|     2|   Jeffy||  4|3.0|     222.0| 99999|  zombie|+---+---+----------+------+--------+*/Dataset<Row> orderItemsDataSet = spark.read().json(Utils.BASE_PATH + "/join/order_items.json");orderItemsDataSet.show();/*+------+---+-----+-------+-----+------+|amount| id| name|orderId|price|userId|+------+---+-----+-------+-----+------+|     4|  1|apple|      1| 20.0|     1||     5|  2| book|      2| 10.0|     1||     1|  3| cake|      3|200.0|     2|+------+---+-----+-------+-----+------+*///1：两张表有相同字段名的join//两张表有相同的join字段名的inner joinusersDataSet.join(ordersDataSet, "userId").show();/*+------+---+--------+---+---+----------+--------+|userId|age|userName| id|qty|totalPrice|userName|+------+---+--------+---+---+----------+--------+|     1| 23|    Join|  2|3.0|      50.0|    Join||     1| 23|    Join|  1|3.0|      80.0|    Join||     2| 45|   Jeffy|  3|3.0|     200.0|   Jeffy|+------+---+--------+---+---+----------+--------+*///joinType: inner、outer、left_outer、right_outer、leftsemi、leftanti//两张表有相同的join字段名的outer join，使的两个字段都只出现一次usersDataSet.join(ordersDataSet, usersDataSet.col("userId").equalTo(ordersDataSet.col("userId")), "outer").show();/*+----+------+--------+----+----+----------+------+--------+| age|userId|userName|  id| qty|totalPrice|userId|userName|+----+------+--------+----+----+----------+------+--------+|  23|     3|    Katy|null|null|      null|  null|    null||  43|     5|      Av|null|null|      null|  null|    null||  23|     1|    Join|   1| 3.0|      80.0|     1|    Join||  23|     1|    Join|   2| 3.0|      50.0|     1|    Join||  21|     4|    Mike|null|null|      null|  null|    null||null|  null|    null|   4| 3.0|     222.0| 99999|  zombie||  45|     2|   Jeffy|   3| 3.0|     200.0|     2|   Jeffy|+----+------+--------+----+----+----------+------+--------+*/usersDataSet.join(ordersDataSet, usersDataSet.col("userId").equalTo(ordersDataSet.col("userId")), "left_outer").show();/*+---+------+--------+----+----+----------+------+--------+|age|userId|userName|  id| qty|totalPrice|userId|userName|+---+------+--------+----+----+----------+------+--------+| 23|     1|    Join|   2| 3.0|      50.0|     1|    Join|| 23|     1|    Join|   1| 3.0|      80.0|     1|    Join|| 23|     3|    Katy|null|null|      null|  null|    null|| 21|     4|    Mike|null|null|      null|  null|    null|| 45|     2|   Jeffy|   3| 3.0|     200.0|     2|   Jeffy|| 43|     5|      Av|null|null|      null|  null|    null|+---+------+--------+----+----+----------+------+--------+*/usersDataSet.join(ordersDataSet, usersDataSet.col("userId").equalTo(ordersDataSet.col("userId")), "right_outer").show();/*+----+------+--------+---+---+----------+------+--------+| age|userId|userName| id|qty|totalPrice|userId|userName|+----+------+--------+---+---+----------+------+--------+|  23|     1|    Join|  1|3.0|      80.0|     1|    Join||  23|     1|    Join|  2|3.0|      50.0|     1|    Join||  45|     2|   Jeffy|  3|3.0|     200.0|     2|   Jeffy||null|  null|    null|  4|3.0|     222.0| 99999|  zombie|+----+------+--------+---+---+----------+------+--------+*///查询出users中的userId在orders存在的usersusersDataSet.join(ordersDataSet, usersDataSet.col("userId").equalTo(ordersDataSet.col("userId")), "leftsemi").show();/*+---+------+--------+|age|userId|userName|+---+------+--------+| 23|     1|    Join|| 45|     2|   Jeffy|+---+------+--------+*///和leftsemi相反，查询出users中的userId不在orders存在的usersusersDataSet.join(ordersDataSet, usersDataSet.col("userId").equalTo(ordersDataSet.col("userId")), "leftanti").show();/*+---+------+--------+|age|userId|userName|+---+------+--------+| 23|     3|    Katy|| 21|     4|    Mike|| 43|     5|      Av|+---+------+--------+*///2: 两张表中不是根据相同字段名的join，即根据条件来join的//两张表没有相同的join字段名的inner joinDataset<Row> joinResult =ordersDataSet.join(orderItemsDataSet, ordersDataSet.col("id").equalTo(orderItemsDataSet.col("orderId")));joinResult.show();/*+---+---+----------+------+--------+------+---+-----+-------+-----+------+| id|qty|totalPrice|userId|userName|amount| id| name|orderId|price|userId|+---+---+----------+------+--------+------+---+-----+-------+-----+------+|  1|3.0|      80.0|     1|    Join|     4|  1|apple|      1| 20.0|     1||  2|3.0|      50.0|     1|    Join|     5|  2| book|      2| 10.0|     1||  3|3.0|     200.0|     2|   Jeffy|     1|  3| cake|      3|200.0|     2|+---+---+----------+------+--------+------+---+-----+-------+-----+------+*/ordersDataSet.join(orderItemsDataSet, ordersDataSet.col("id").equalTo(orderItemsDataSet.col("orderId")), "outer").show();/*+---+---+----------+------+--------+------+----+-----+-------+-----+------+| id|qty|totalPrice|userId|userName|amount|  id| name|orderId|price|userId|+---+---+----------+------+--------+------+----+-----+-------+-----+------+|  3|3.0|     200.0|     2|   Jeffy|     1|   3| cake|      3|200.0|     2||  1|3.0|      80.0|     1|    Join|     4|   1|apple|      1| 20.0|     1||  4|3.0|     222.0| 99999|  zombie|  null|null| null|   null| null|  null||  2|3.0|      50.0|     1|    Join|     5|   2| book|      2| 10.0|     1|+---+---+----------+------+--------+------+----+-----+-------+-----+------+*/}
}