Subqueries in Apache Spark 2.0

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

1. SQL Subqueries in Apache Spark 2.0
   
2. Hands-on examples of scalar and predicate type of subqueries
   
3. Davies LiuHerman van Hövell by Davies Liu and Herman van Hövell Posted in ENGINEERING BLOG June 17, 2016
   
4. Try this notebook in Databricks
   
5. In the upcoming Apache Spark 2.0 release, we have substantially expanded the SQL standard capabilities. In this brief blog post, we will introduce subqueries in Apache Spark 2.0, including their limitations, potential pitfalls and future expansions, and through a notebook, we will explore both the scalar and predicate type of subqueries, with short examples that you can try yourself.
   
6. 
   
7. A subquery is a query that is nested inside of another query. A subquery as a source (inside a SQL FROM clause) is technically also a subquery, but it is beyond the scope of this post. There are basically two kinds of subqueries: scalar and predicate subqueries. And within scalar and predicate queries, there are uncorrelated scalar and correlated scalar queries and nested predicate queries respectively.
   
8. 
   
9. For brevity, we will let you jump and explore the notebook, which is more an interactive experience rather than an exposition here in the blog. Click on this diagram below to view and explore the subquery notebook with Apache Spark 2.0 preview on Databricks.

复制代码

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：Apache Spark Queries apache Spark Park

1. %scala
   
2. import org.apache.spark.sql.functions._
   
3. val employee = spark.range(0, 10).select($"id".as("employee_id"), (rand() * 3).cast("int").as("dep_id"), (rand() * 40 + 20).cast("int").as("age"))
   
4. val visit = spark.range(0, 100).select($"id".as("visit_id"), when(rand() < 0.95, ($"id" % 8)).as("employee_id"))
   
5. val appointment = spark.range(0, 100).select($"id".as("appointment_id"), when(rand() < 0.95, ($"id" % 7)).as("employee_id"))
   
6. employee.createOrReplaceTempView("employee")
   
7. visit.createOrReplaceTempView("visit")
   
8. appointment.createOrReplaceTempView("appointment")

复制代码

1. Uncorrelated Scalar Subqueries
   
2. An uncorrelated subquery returns the same single value for all records in a query. Uncorrelated subqueries are executed by the Spark enging before the main query is executed. The SQL below shows an example of an uncorrelated scalar subquery, here we add the maximum age in table employee to the select.
   
3. %sql
   
4. SELECT  employee_id,
   
5.         age,
   
6.         (SELECT MAX(age) FROM employee) max_age
   
7. FROM    employee

复制代码

1. Correlated Scalar Subqueries
   
2. Subqueries can be correlated, this means that the subquery contains references to the outer query. These outer references are typically used in filter clauses (SQL WHERE clause). Spark 2.0 currently only supports this case. The SQL below shows an example of a correlated scalar subquery, here we add the maximum age in an employee’s department to the select list using A.dep_id = B.dep_id as the correlated condition.
   
3. %sql
   
4. SELECT   A.dep_id,
   
5.          A.employee_id,
   
6.          A.age,
   
7.          (SELECT  MAX(age) FROM employee B WHERE A.dep_id = B.dep_id) max_age
   
8. FROM     employee A
   
9. ORDER BY 1,2

复制代码