Spark SQL Guide

Migration Guide: SQL, Datasets and DataFrame

Upgrading from Spark SQL 3.5 to 4.0

Upgrading from Spark SQL 3.5.1 to 3.5.2

Upgrading from Spark SQL 3.5.0 to 3.5.1

Upgrading from Spark SQL 3.4 to 3.5

Upgrading from Spark SQL 3.3 to 3.4

Upgrading from Spark SQL 3.2 to 3.3

Upgrading from Spark SQL 3.1 to 3.2

Upgrading from Spark SQL 3.0 to 3.1

Upgrading from Spark SQL 3.0.1 to 3.0.2

Upgrading from Spark SQL 3.0 to 3.0.1

Upgrading from Spark SQL 2.4 to 3.0

Dataset/DataFrame APIs

DDL Statements

UDFs and Built-in Functions

Query Engine

Data Sources

Others

Upgrading from Spark SQL 2.4.7 to 2.4.8

Upgrading from Spark SQL 2.4.5 to 2.4.6

Upgrading from Spark SQL 2.4.4 to 2.4.5

Upgrading from Spark SQL 2.4.3 to 2.4.4

Upgrading from Spark SQL 2.4 to 2.4.1

Upgrading from Spark SQL 2.3 to 2.4

Upgrading from Spark SQL 2.2 to 2.3

</thead> <tr> <td> NullType </td> <td>NullType</td> <td>IntegerType</td> <td>LongType</td> <td>DecimalType(38,0)</td> <td>DoubleType</td> <td>DateType</td> <td>TimestampType</td> <td>StringType</td> </tr> <tr> <td> IntegerType </td> <td>IntegerType</td> <td>IntegerType</td> <td>LongType</td> <td>DecimalType(38,0)</td> <td>DoubleType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> </tr> <tr> <td> LongType </td> <td>LongType</td> <td>LongType</td> <td>LongType</td> <td>DecimalType(38,0)</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> </tr> <tr> <td> DecimalType(38,0)* </td> <td>DecimalType(38,0)</td> <td>DecimalType(38,0)</td> <td>DecimalType(38,0)</td> <td>DecimalType(38,0)</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> </tr> <tr> <td> DoubleType </td> <td>DoubleType</td> <td>DoubleType</td> <td>StringType</td> <td>StringType</td> <td>DoubleType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> </tr> <tr> <td> DateType </td> <td>DateType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>DateType</td> <td>TimestampType</td> <td>StringType</td> </tr> <tr> <td> TimestampType </td> <td>TimestampType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>TimestampType</td> <td>TimestampType</td> <td>StringType</td> </tr> <tr> <td> StringType </td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> <td>StringType</td> </tr> </table>

Note that, for <b>DecimalType(38,0)*</b>, the table above intentionally does not cover all other combinations of scales and precisions because currently we only infer decimal type like `BigInteger`/`BigInt`. For example, 1.1 is inferred as double type.

Upgrading from Spark SQL 2.1 to 2.2

Upgrading from Spark SQL 2.0 to 2.1

Upgrading from Spark SQL 1.6 to 2.0

Upgrading from Spark SQL 1.5 to 1.6

   ./sbin/start-thriftserver.sh \
     --conf spark.sql.hive.thriftServer.singleSession=true \
     ...

Upgrading from Spark SQL 1.4 to 1.5

Upgrading from Spark SQL 1.3 to 1.4

DataFrame data reader/writer interface

Based on user feedback, we created a new, more fluid API for reading data in (SQLContext.read) and writing data out (DataFrame.write), and deprecated the old APIs (e.g., SQLContext.parquetFile, SQLContext.jsonFile).

See the API docs for SQLContext.read ( Scala, Java, Python ) and DataFrame.write ( Scala, Java, Python ) more information.

DataFrame.groupBy retains grouping columns

Based on user feedback, we changed the default behavior of DataFrame.groupBy().agg() to retain the grouping columns in the resulting DataFrame. To keep the behavior in 1.3, set spark.sql.retainGroupColumns to false.

import pyspark.sql.functions as func

# In 1.3.x, in order for the grouping column "department" to show up,
# it must be included explicitly as part of the agg function call.
df.groupBy("department").agg(df["department"], func.max("age"), func.sum("expense"))

# In 1.4+, grouping column "department" is included automatically.
df.groupBy("department").agg(func.max("age"), func.sum("expense"))

# Revert to 1.3.x behavior (not retaining grouping column) by:
sqlContext.setConf("spark.sql.retainGroupColumns", "false")
// In 1.3.x, in order for the grouping column "department" to show up,
// it must be included explicitly as part of the agg function call.
df.groupBy("department").agg($"department", max("age"), sum("expense"))

// In 1.4+, grouping column "department" is included automatically.
df.groupBy("department").agg(max("age"), sum("expense"))

// Revert to 1.3 behavior (not retaining grouping column) by:
sqlContext.setConf("spark.sql.retainGroupColumns", "false")
// In 1.3.x, in order for the grouping column "department" to show up,
// it must be included explicitly as part of the agg function call.
df.groupBy("department").agg(col("department"), max("age"), sum("expense"));

// In 1.4+, grouping column "department" is included automatically.
df.groupBy("department").agg(max("age"), sum("expense"));

// Revert to 1.3 behavior (not retaining grouping column) by:
sqlContext.setConf("spark.sql.retainGroupColumns", "false");

Behavior change on DataFrame.withColumn

Prior to 1.4, DataFrame.withColumn() supports adding a column only. The column will always be added as a new column with its specified name in the result DataFrame even if there may be any existing columns of the same name. Since 1.4, DataFrame.withColumn() supports adding a column of a different name from names of all existing columns or replacing existing columns of the same name.

Note that this change is only for Scala API, not for PySpark and SparkR.

Upgrading from Spark SQL 1.0-1.2 to 1.3

In Spark 1.3 we removed the “Alpha” label from Spark SQL and as part of this did a cleanup of the available APIs. From Spark 1.3 onwards, Spark SQL will provide binary compatibility with other releases in the 1.X series. This compatibility guarantee excludes APIs that are explicitly marked as unstable (i.e., DeveloperAPI or Experimental).

Rename of SchemaRDD to DataFrame

The largest change that users will notice when upgrading to Spark SQL 1.3 is that SchemaRDD has been renamed to DataFrame. This is primarily because DataFrames no longer inherit from RDD directly, but instead provide most of the functionality that RDDs provide though their own implementation. DataFrames can still be converted to RDDs by calling the .rdd method.

In Scala, there is a type alias from SchemaRDD to DataFrame to provide source compatibility for some use cases. It is still recommended that users update their code to use DataFrame instead. Java and Python users will need to update their code.

Unification of the Java and Scala APIs

Prior to Spark 1.3 there were separate Java compatible classes (JavaSQLContext and JavaSchemaRDD) that mirrored the Scala API. In Spark 1.3 the Java API and Scala API have been unified. Users of either language should use SQLContext and DataFrame. In general these classes try to use types that are usable from both languages (i.e. Array instead of language-specific collections). In some cases where no common type exists (e.g., for passing in closures or Maps) function overloading is used instead.

Additionally, the Java specific types API has been removed. Users of both Scala and Java should use the classes present in org.apache.spark.sql.types to describe schema programmatically.

Isolation of Implicit Conversions and Removal of dsl Package (Scala-only)

Many of the code examples prior to Spark 1.3 started with import sqlContext._, which brought all of the functions from sqlContext into scope. In Spark 1.3 we have isolated the implicit conversions for converting RDDs into DataFrames into an object inside of the SQLContext. Users should now write import sqlContext.implicits._.

Additionally, the implicit conversions now only augment RDDs that are composed of Products (i.e., case classes or tuples) with a method toDF, instead of applying automatically.

When using function inside of the DSL (now replaced with the DataFrame API) users used to import org.apache.spark.sql.catalyst.dsl. Instead the public dataframe functions API should be used: import org.apache.spark.sql.functions._.

Removal of the type aliases in org.apache.spark.sql for DataType (Scala-only)

Spark 1.3 removes the type aliases that were present in the base sql package for DataType. Users should instead import the classes in org.apache.spark.sql.types

UDF Registration Moved to sqlContext.udf (Java & Scala)

Functions that are used to register UDFs, either for use in the DataFrame DSL or SQL, have been moved into the udf object in SQLContext.

sqlContext.udf.register("strLen", (s: String) => s.length())
sqlContext.udf().register("strLen", (String s) -> s.length(), DataTypes.IntegerType);

Python UDF registration is unchanged.

Compatibility with Apache Hive

Spark SQL is designed to be compatible with the Hive Metastore, SerDes and UDFs. Currently, Hive SerDes and UDFs are based on built-in Hive, and Spark SQL can be connected to different versions of Hive Metastore (from 2.0.0 to 2.3.10 and 3.0.0 to 3.1.3. Also see Interacting with Different Versions of Hive Metastore).

Deploying in Existing Hive Warehouses

The Spark SQL Thrift JDBC server is designed to be “out of the box” compatible with existing Hive installations. You do not need to modify your existing Hive Metastore or change the data placement or partitioning of your tables.

Supported Hive Features

Spark SQL supports the vast majority of Hive features, such as:

Unsupported Hive Functionality

Below is a list of Hive features that we don’t support yet. Most of these features are rarely used in Hive deployments.

Esoteric Hive Features

Hive Input/Output Formats

Hive Optimizations

A handful of Hive optimizations are not yet included in Spark. Some of these (such as indexes) are less important due to Spark SQL’s in-memory computational model. Others are slotted for future releases of Spark SQL.

Hive UDF/UDTF/UDAF

Not all the APIs of the Hive UDF/UDTF/UDAF are supported by Spark SQL. Below are the unsupported APIs:

Incompatible Hive UDF

Below are the scenarios in which Hive and Spark generate different results: