kode python Databricks UDF Python Pyspark import udf Pyspark SQL types Databricks SQL UDF Pyspark test UDF Pyspark UDF slow

Cara menggunakan databricks sql python udf

Spark SQL, DataFrames and Datasets Guide

  • Overview
    • SQL
    • Datasets and DataFrames
  • Getting Started
    • Starting Point: SparkSession
    • Creating DataFrames
    • Untyped Dataset Operations [aka DataFrame Operations]
    • Running SQL Queries Programmatically
    • Global Temporary View
    • Creating Datasets
    • Interoperating with RDDs
      • Inferring the Schema Using Reflection
      • Programmatically Specifying the Schema
    • Aggregations
      • Untyped User-Defined Aggregate Functions
      • Type-Safe User-Defined Aggregate Functions
  • Data Sources
    • Generic Load/Save Functions
      • Manually Specifying Options
      • Run SQL on files directly
      • Save Modes
      • Saving to Persistent Tables
      • Bucketing, Sorting and Partitioning
    • Parquet Files
      • Loading Data Programmatically
      • Partition Discovery
      • Schema Merging
      • Hive metastore Parquet table conversion
        • Hive/Parquet Schema Reconciliation
        • Metadata Refreshing
      • Configuration
    • ORC Files
    • JSON Datasets
    • Hive Tables
      • Specifying storage format for Hive tables
      • Interacting with Different Versions of Hive Metastore
    • JDBC To Other Databases
    • Troubleshooting
  • Performance Tuning
    • Caching Data In Memory
    • Other Configuration Options
    • Broadcast Hint for SQL Queries
  • Distributed SQL Engine
    • Running the Thrift JDBC/ODBC server
    • Running the Spark SQL CLI
  • PySpark Usage Guide for Pandas with Apache Arrow
    • Apache Arrow in Spark
      • Ensure PyArrow Installed
    • Enabling for Conversion to/from Pandas
    • Pandas UDFs [a.k.a. Vectorized UDFs]
      • Scalar
      • Grouped Map
    • Usage Notes
      • Supported SQL Types
      • Setting Arrow Batch Size
      • Timestamp with Time Zone Semantics
  • Migration Guide
    • Upgrading From Spark SQL 2.3.0 to 2.3.1 and above
    • Upgrading From Spark SQL 2.2 to 2.3
    • 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
    • Upgrading From Spark SQL 1.4 to 1.5
    • Upgrading from Spark SQL 1.3 to 1.4
      • DataFrame data reader/writer interface
      • DataFrame.groupBy retains grouping columns
      • Behavior change on DataFrame.withColumn
    • Upgrading from Spark SQL 1.0-1.2 to 1.3
      • Rename of SchemaRDD to DataFrame
      • Unification of the Java and Scala APIs
      • Isolation of Implicit Conversions and Removal of dsl Package [Scala-only]
      • Removal of the type aliases in org.apache.spark.sql for DataType [Scala-only]
      • UDF Registration Moved to sqlContext.udf [Java & Scala]
      • Python DataTypes No Longer Singletons
    • Compatibility with Apache Hive
      • Deploying in Existing Hive Warehouses
      • Supported Hive Features
      • Unsupported Hive Functionality
      • Incompatible Hive UDF
  • Reference
    • Data Types
    • NaN Semantics

Overview

Spark SQL is a Spark module for structured data processing. Unlike the basic Spark RDD API, the interfaces provided by Spark SQL provide Spark with more information about the structure of both the data and the computation being performed. Internally, Spark SQL uses this extra information to perform extra optimizations. There are several ways to interact with Spark SQL including SQL and the Dataset API. When computing a result the same execution engine is used, independent of which API/language you are using to express the computation. This unification means that developers can easily switch back and forth between different APIs based on which provides the most natural way to express a given transformation.

All of the examples on this page use sample data included in the Spark distribution and can be run in the spark-shell, pyspark shell, or sparkR shell.

One use of Spark SQL is to execute SQL queries. Spark SQL can also be used to read data from an existing Hive installation. For more on how to configure this feature, please refer to the Hive Tables section. When running SQL from within another programming language the results will be returned as a Dataset/DataFrame. You can also interact with the SQL interface using the command-line or over JDBC/ODBC.

Datasets and DataFrames

A Dataset is a distributed collection of data. Dataset is a new interface added in Spark 1.6 that provides the benefits of RDDs [strong typing, ability to use powerful lambda functions] with the benefits of Spark SQL’s optimized execution engine. A Dataset can be constructed from JVM objects and then manipulated using functional transformations [map, flatMap, filter, etc.]. The Dataset API is available in Scala and Java. Python does not have the support for the Dataset API. But due to Python’s dynamic nature, many of the benefits of the Dataset API are already available [i.e. you can access the field of a row by name naturally row.columnName]. The case for R is similar.

A DataFrame is a Dataset organized into named columns. It is conceptually equivalent to a table in a relational database or a data frame in R/Python, but with richer optimizations under the hood. DataFrames can be constructed from a wide array of sources such as: structured data files, tables in Hive, external databases, or existing RDDs. The DataFrame API is available in Scala, Java, Python, and R. In Scala and Java, a DataFrame is represented by a Dataset of Rows. In the Scala API, DataFrame is simply a type alias of Dataset[Row]. While, in Java API, users need to use Dataset to represent a DataFrame.

Throughout this document, we will often refer to Scala/Java Datasets of Rows as DataFrames.

Getting Started

Starting Point: SparkSession

The entry point into all functionality in Spark is the SparkSession class. To create a basic SparkSession, just use SparkSession.builder[]:

import org.apache.spark.sql.SparkSession

val spark = SparkSession
  .builder[]
  .appName["Spark SQL basic example"]
  .config["spark.some.config.option", "some-value"]
  .getOrCreate[]

// For implicit conversions like converting RDDs to DataFrames
import spark.implicits._

Find full example code at "examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala" in the Spark repo.

The entry point into all functionality in Spark is the SparkSession class. To create a basic SparkSession, just use SparkSession.builder[]:

import org.apache.spark.sql.SparkSession;

SparkSession spark = SparkSession
  .builder[]
  .appName["Java Spark SQL basic example"]
  .config["spark.some.config.option", "some-value"]
  .getOrCreate[];

Find full example code at "examples/src/main/java/org/apache/spark/examples/sql/JavaSparkSQLExample.java" in the Spark repo.

The entry point into all functionality in Spark is the SparkSession class. To create a basic SparkSession, just use SparkSession.builder:

from pyspark.sql import SparkSession

spark = SparkSession \
    .builder \
    .appName["Python Spark SQL basic example"] \
    .config["spark.some.config.option", "some-value"] \
    .getOrCreate[]

Find full example code at "examples/src/main/python/sql/basic.py" in the Spark repo.

The entry point into all functionality in Spark is the SparkSession class. To initialize a basic SparkSession, just call sparkR.session[]:

sparkR.session[appName = "R Spark SQL basic example", sparkConfig = list[spark.some.config.option = "some-value"]]

Find full example code at "examples/src/main/r/RSparkSQLExample.R" in the Spark repo.

Note that when invoked for the first time, sparkR.session[] initializes a global SparkSession singleton instance, and always returns a reference to this instance for successive invocations. In this way, users only need to initialize the SparkSession once, then SparkR functions like read.df will be able to access this global instance implicitly, and users don’t need to pass the SparkSession instance around.

SparkSession in Spark 2.0 provides builtin support for Hive features including the ability to write queries using HiveQL, access to Hive UDFs, and the ability to read data from Hive tables. To use these features, you do not need to have an existing Hive setup.

Creating DataFrames

With a SparkSession, applications can create DataFrames from an existing RDD, from a Hive table, or from Spark data sources.

As an example, the following creates a DataFrame based on the content of a JSON file:

val df = spark.read.json["examples/src/main/resources/people.json"]

// Displays the content of the DataFrame to stdout
df.show[]
// +----+-------+
// | age|   name|
// +----+-------+
// |null|Michael|
// |  30|   Andy|
// |  19| Justin|
// +----+-------+

Find full example code at "examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala" in the Spark repo.

With a SparkSession, applications can create DataFrames from an existing RDD, from a Hive table, or from Spark data sources.

As an example, the following creates a DataFrame based on the content of a JSON file:

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;

Dataset df = spark.read[].json["examples/src/main/resources/people.json"];

// Displays the content of the DataFrame to stdout
df.show[];
// +----+-------+
// | age|   name|
// +----+-------+
// |null|Michael|
// |  30|   Andy|
// |  19| Justin|
// +----+-------+

Find full example code at "examples/src/main/java/org/apache/spark/examples/sql/JavaSparkSQLExample.java" in the Spark repo.

With a SparkSession, applications can create DataFrames from an existing RDD, from a Hive table, or from Spark data sources.

As an example, the following creates a DataFrame based on the content of a JSON file:

# spark is an existing SparkSession
df = spark.read.json["examples/src/main/resources/people.json"]
# Displays the content of the DataFrame to stdout
df.show[]
# +----+-------+
# | age|   name|
# +----+-------+
# |null|Michael|
# |  30|   Andy|
# |  19| Justin|
# +----+-------+

Find full example code at "examples/src/main/python/sql/basic.py" in the Spark repo.

With a SparkSession, applications can create DataFrames from a local R data.frame, from a Hive table, or from Spark data sources.

As an example, the following creates a DataFrame based on the content of a JSON file:

df  21].show[]
// +---+----+
// |age|name|
// +---+----+
// | 30|Andy|
// +---+----+

// Count people by age
df.groupBy["age"].count[].show[]
// +----+-----+
// | age|count|
// +----+-----+
// |  19|    1|
// |null|    1|
// |  30|    1|
// +----+-----+

Find full example code at "examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala" in the Spark repo.

For a complete list of the types of operations that can be performed on a Dataset refer to the API Documentation.

In addition to simple column references and expressions, Datasets also have a rich library of functions including string manipulation, date arithmetic, common math operations and more. The complete list is available in the DataFrame Function Reference.

// col["..."] is preferable to df.col["..."]
import static org.apache.spark.sql.functions.col;

// 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[col["name"], col["age"].plus[1]].show[];
// +-------+---------+
// |   name|[age + 1]|
// +-------+---------+
// |Michael|     null|
// |   Andy|       31|
// | Justin|       20|
// +-------+---------+

// Select people older than 21
df.filter[col["age"].gt[21]].show[];
// +---+----+
// |age|name|
// +---+----+
// | 30|Andy|
// +---+----+

// Count people by age
df.groupBy["age"].count[].show[];
// +----+-----+
// | age|count|
// +----+-----+
// |  19|    1|
// |null|    1|
// |  30|    1|
// +----+-----+

Find full example code at "examples/src/main/java/org/apache/spark/examples/sql/JavaSparkSQLExample.java" in the Spark repo.

For a complete list of the types of operations that can be performed on a Dataset refer to the API Documentation.

In addition to simple column references and expressions, Datasets also have a rich library of functions including string manipulation, date arithmetic, common math operations and more. The complete list is available in the DataFrame Function Reference.

In Python it’s possible to access a DataFrame’s columns either by attribute [df.age] or by indexing [df['age']]. While the former is convenient for interactive data exploration, users are highly encouraged to use the latter form, which is future proof and won’t break with column names that are also attributes on the DataFrame class.

# spark, df are from the previous example
# 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['name'], df['age'] + 1].show[]
# +-------+---------+
# |   name|[age + 1]|
# +-------+---------+
# |Michael|     null|
# |   Andy|       31|
# | Justin|       20|
# +-------+---------+

# Select people older than 21
df.filter[df['age'] > 21].show[]
# +---+----+
# |age|name|
# +---+----+
# | 30|Andy|
# +---+----+

# Count people by age
df.groupBy["age"].count[].show[]
# +----+-----+
# | age|count|
# +----+-----+
# |  19|    1|
# |null|    1|
# |  30|    1|
# +----+-----+

Find full example code at "examples/src/main/python/sql/basic.py" in the Spark repo.

For a complete list of the types of operations that can be performed on a DataFrame refer to the API Documentation.

In addition to simple column references and expressions, DataFrames also have a rich library of functions including string manipulation, date arithmetic, common math operations and more. The complete list is available in the DataFrame Function Reference.

# Create the DataFrame
df  21]]
##   age name
## 1  30 Andy

# Count people by age
head[count[groupBy[df, "age"]]]
##   age count
## 1  19     1
## 2  NA     1
## 3  30     1

Find full example code at "examples/src/main/r/RSparkSQLExample.R" in the Spark repo.

For a complete list of the types of operations that can be performed on a DataFrame refer to the API Documentation.

In addition to simple column references and expressions, DataFrames also have a rich library of functions including string manipulation, date arithmetic, common math operations and more. The complete list is available in the DataFrame Function Reference.

Running SQL Queries Programmatically

The sql function on a SparkSession enables applications to run SQL queries programmatically and returns the result as a DataFrame.

// Register the DataFrame as a SQL temporary view
df.createOrReplaceTempView["people"]

val sqlDF = spark.sql["SELECT * FROM people"]
sqlDF.show[]
// +----+-------+
// | age|   name|
// +----+-------+
// |null|Michael|
// |  30|   Andy|
// |  19| Justin|
// +----+-------+

Find full example code at "examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala" in the Spark repo.

The sql function on a SparkSession enables applications to run SQL queries programmatically and returns the result as a Dataset.

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;

// Register the DataFrame as a SQL temporary view
df.createOrReplaceTempView["people"];

Dataset sqlDF = spark.sql["SELECT * FROM people"];
sqlDF.show[];
// +----+-------+
// | age|   name|
// +----+-------+
// |null|Michael|
// |  30|   Andy|
// |  19| Justin|
// +----+-------+

Find full example code at "examples/src/main/java/org/apache/spark/examples/sql/JavaSparkSQLExample.java" in the Spark repo.

The sql function on a SparkSession enables applications to run SQL queries programmatically and returns the result as a DataFrame.

# Register the DataFrame as a SQL temporary view
df.createOrReplaceTempView["people"]

sqlDF = spark.sql["SELECT * FROM people"]
sqlDF.show[]
# +----+-------+
# | age|   name|
# +----+-------+
# |null|Michael|
# |  30|   Andy|
# |  19| Justin|
# +----+-------+

Find full example code at "examples/src/main/python/sql/basic.py" in the Spark repo.

The sql function enables applications to run SQL queries programmatically and returns the result as a SparkDataFrame.

df  value + 1,
    integerEncoder];
transformedDS.collect[]; // Returns [2, 3, 4]

// DataFrames can be converted to a Dataset by providing a class. Mapping based on name
String path = "examples/src/main/resources/people.json";
Dataset peopleDS = spark.read[].json[path].as[personEncoder];
peopleDS.show[];
// +----+-------+
// | age|   name|
// +----+-------+
// |null|Michael|
// |  30|   Andy|
// |  19| Justin|
// +----+-------+

Find full example code at "examples/src/main/java/org/apache/spark/examples/sql/JavaSparkSQLExample.java" in the Spark repo.

Interoperating with RDDs

Spark SQL supports two different methods for converting existing RDDs into Datasets. The first method uses reflection to infer the schema of an RDD that contains specific types of objects. This reflection based approach leads to more concise code and works well when you already know the schema while writing your Spark application.

The second method for creating Datasets is through a programmatic interface that allows you to construct a schema and then apply it to an existing RDD. While this method is more verbose, it allows you to construct Datasets when the columns and their types are not known until runtime.

Inferring the Schema Using Reflection

The Scala interface for Spark SQL supports automatically converting an RDD containing case classes to a DataFrame. The case class defines the schema of the table. The names of the arguments to the case class are read using reflection and become the names of the columns. Case classes can also be nested or contain complex types such as Seqs or Arrays. This RDD can be implicitly converted to a DataFrame and then be registered as a table. Tables can be used in subsequent SQL statements.

// For implicit conversions from RDDs to DataFrames
import spark.implicits._

// Create an RDD of Person objects from a text file, convert it to a Dataframe
val peopleDF = spark.sparkContext
  .textFile["examples/src/main/resources/people.txt"]
  .map[_.split[","]]
  .map[attributes => Person[attributes[0], attributes[1].trim.toInt]]
  .toDF[]
// Register the DataFrame as a temporary view
peopleDF.createOrReplaceTempView["people"]

// SQL statements can be run by using the sql methods provided by Spark
val teenagersDF = spark.sql["SELECT name, age FROM people WHERE age BETWEEN 13 AND 19"]

// The columns of a row in the result can be accessed by field index
teenagersDF.map[teenager => "Name: " + teenager[0]].show[]
// +------------+
// |       value|
// +------------+
// |Name: Justin|
// +------------+

// or by field name
teenagersDF.map[teenager => "Name: " + teenager.getAs[String]["name"]].show[]
// +------------+
// |       value|
// +------------+
// |Name: Justin|
// +------------+

// No pre-defined encoders for Dataset[Map[K,V]], define explicitly
implicit val mapEncoder = org.apache.spark.sql.Encoders.kryo[Map[String, Any]]
// Primitive types and case classes can be also defined as
// implicit val stringIntMapEncoder: Encoder[Map[String, Any]] = ExpressionEncoder[]

// row.getValuesMap[T] retrieves multiple columns at once into a Map[String, T]
teenagersDF.map[teenager => teenager.getValuesMap[Any][List["name", "age"]]].collect[]
// Array[Map["name" -> "Justin", "age" -> 19]]

Find full example code at "examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala" in the Spark repo.

Spark SQL supports automatically converting an RDD of JavaBeans into a DataFrame. The BeanInfo, obtained using reflection, defines the schema of the table. Currently, Spark SQL does not support JavaBeans that contain Map field[s]. Nested JavaBeans and List or Array fields are supported though. You can create a JavaBean by creating a class that implements Serializable and has getters and setters for all of its fields.

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;

// Create an RDD of Person objects from a text file
JavaRDD peopleRDD = spark.read[]
  .textFile["examples/src/main/resources/people.txt"]
  .javaRDD[]
  .map[line -> {
    String[] parts = line.split[","];
    Person person = new Person[];
    person.setName[parts[0]];
    person.setAge[Integer.parseInt[parts[1].trim[]]];
    return person;
  }];

// Apply a schema to an RDD of JavaBeans to get a DataFrame
Dataset peopleDF = spark.createDataFrame[peopleRDD, Person.class];
// Register the DataFrame as a temporary view
peopleDF.createOrReplaceTempView["people"];

// SQL statements can be run by using the sql methods provided by spark
Dataset teenagersDF = spark.sql["SELECT name FROM people WHERE age BETWEEN 13 AND 19"];

// The columns of a row in the result can be accessed by field index
Encoder stringEncoder = Encoders.STRING[];
Dataset teenagerNamesByIndexDF = teenagersDF.map[
    [MapFunction] row -> "Name: " + row.getString[0],
    stringEncoder];
teenagerNamesByIndexDF.show[];
// +------------+
// |       value|
// +------------+
// |Name: Justin|
// +------------+

// or by field name
Dataset teenagerNamesByFieldDF = teenagersDF.map[
    [MapFunction] row -> "Name: " + row.getAs["name"],
    stringEncoder];
teenagerNamesByFieldDF.show[];
// +------------+
// |       value|
// +------------+
// |Name: Justin|
// +------------+

Find full example code at "examples/src/main/java/org/apache/spark/examples/sql/JavaSparkSQLExample.java" in the Spark repo.

Spark SQL can convert an RDD of Row objects to a DataFrame, inferring the datatypes. Rows are constructed by passing a list of key/value pairs as kwargs to the Row class. The keys of this list define the column names of the table, and the types are inferred by sampling the whole dataset, similar to the inference that is performed on JSON files.

from pyspark.sql import Row

sc = spark.sparkContext

# Load a text file and convert each line to a Row.
lines = sc.textFile["examples/src/main/resources/people.txt"]
parts = lines.map[lambda l: l.split[","]]
people = parts.map[lambda p: Row[name=p[0], age=int[p[1]]]]

# Infer the schema, and register the DataFrame as a table.
schemaPeople = spark.createDataFrame[people]
schemaPeople.createOrReplaceTempView["people"]

# SQL can be run over DataFrames that have been registered as a table.
teenagers = spark.sql["SELECT name FROM people WHERE age >= 13 AND age = 13 AND age = 13 AND age = 13 AND age

Bài Viết Liên Quan

What are the privileges provided by mysql?
Php vs python for backend
Membuat cloud storage dengan php
Where can we find php ini file?
How do i know my cnf is mysql?
Jenis server yang dapat digunakan bersama php adalah
Php parse_ini_file example
Google map api php mysql
Cara menggunakan selected option php
Print numbers in columns python
How can i use older version of php in xampp?
Where is mysql jdbc driver located?
Menu apakah yang digunakan ketika kita ingin mengupload file html yang kita buat ke hosting tersebut jika menggunakan cpanel?
Javascript practice exercises with solutions pdf
Php form upload file send email attachment
Mysql for visual studio 1.2 6
Cara menggunakan if else javascript
Apa itu def di python?
How to print two columns from a dataframe in python
Change website language dynamically in php

Toplist mới

#1
Top 7 luas segitiga berikut adalah 130 cm k 300 cm - 120 cm 2023
5 tháng trước
#2
Top 16 negara asean yang memiliki jumlah penduduk paling sedikit adalah 2023
5 tháng trước
#3
Top 6 poh larutan cuka 0 1 m ka = 10 ⁻ ⁵ adalah 2023
5 tháng trước
#4
Top 8 sistem pengangkatan pegawai berdasarkan kecakapan atau kompetensi pegawai adalah 2023
5 tháng trước
#5
Top 8 proses pertama kali yang terjadi pada saat pembentukan urine adalah 2023
5 tháng trước
#6
Top 8 contoh diagonal bidang 2023
5 tháng trước
#7
Top 5 contoh ideologi transnasional 2023
5 tháng trước
#8
Top 7 kapur gamping dalam alat penjernih air berfungsi untuk …. 2023
5 tháng trước
#9
Top 6 rumus molekul senyawa dengan rumus empiris c2h2o dan mr 84 adalah 2023
5 tháng trước

Bài mới nhất

Chủ Đề

Kiat Bagus Cara Belajar Location Jelaskan Kesehatan dan kecantikan Bagaimana Teknologi Apa arti Arti kata Sebutkan Top List Toplist Apa yang Permainan Mengapa disebut Belajar dengan baik Membandingkan Perbedaan Berikut ini Makanan lezat Dimaksud Tentang Facebook Konstruksi Berikut yang Daftar Teratas javascript