site/docs/2.4.0/sql-programming-guide.html - spark-website - Git at Google


 <!DOCTYPE html>
 <!--[if lt IE 7]>      <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
 <!--[if IE 7]>         <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
 <!--[if IE 8]>         <html class="no-js lt-ie9"> <![endif]-->
 <!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
     <head>
         <meta charset="utf-8">
         <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
         <title>Spark SQL and DataFrames - Spark 2.4.0 Documentation</title>


         <link rel="stylesheet" href="css/bootstrap.min.css">
         <style>
             body {
                 padding-top: 60px;
                 padding-bottom: 40px;
             }
         </style>
         <meta name="viewport" content="width=device-width">
         <link rel="stylesheet" href="css/bootstrap-responsive.min.css">
         <link rel="stylesheet" href="css/main.css">

         <script src="js/vendor/modernizr-2.6.1-respond-1.1.0.min.js"></script>

         <link rel="stylesheet" href="css/pygments-default.css">


         <!-- Google analytics script -->
         <script type="text/javascript">
           var _gaq = _gaq || [];
           _gaq.push(['_setAccount', 'UA-32518208-2']);
           _gaq.push(['_trackPageview']);

           (function() {
             var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
             ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
             var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
           })();
         </script>


     </head>
     <body>
         <!--[if lt IE 7]>
             <p class="chromeframe">You are using an outdated browser. <a href="https://browsehappy.com/">Upgrade your browser today</a> or <a href="http://www.google.com/chromeframe/?redirect=true">install Google Chrome Frame</a> to better experience this site.</p>
         <![endif]-->

         <!-- This code is taken from http://twitter.github.com/bootstrap/examples/hero.html -->

         <div class="navbar navbar-fixed-top" id="topbar">
             <div class="navbar-inner">
                 <div class="container">
                     <div class="brand"><a href="index.html">
                       <img src="img/spark-logo-hd.png" style="height:50px;"/></a><span class="version">2.4.0</span>
                     </div>
                     <ul class="nav">
                         <!--TODO(andyk): Add class="active" attribute to li some how.-->
                         <li><a href="index.html">Overview</a></li>

                         <li class="dropdown">
                             <a href="#" class="dropdown-toggle" data-toggle="dropdown">Programming Guides<b class="caret"></b></a>
                             <ul class="dropdown-menu">
                                 <li><a href="quick-start.html">Quick Start</a></li>
                                 <li><a href="rdd-programming-guide.html">RDDs, Accumulators, Broadcasts Vars</a></li>
                                 <li><a href="sql-programming-guide.html">SQL, DataFrames, and Datasets</a></li>
                                 <li><a href="structured-streaming-programming-guide.html">Structured Streaming</a></li>
                                 <li><a href="streaming-programming-guide.html">Spark Streaming (DStreams)</a></li>
                                 <li><a href="ml-guide.html">MLlib (Machine Learning)</a></li>
                                 <li><a href="graphx-programming-guide.html">GraphX (Graph Processing)</a></li>
                                 <li><a href="sparkr.html">SparkR (R on Spark)</a></li>
                             </ul>
                         </li>

                         <li class="dropdown">
                             <a href="#" class="dropdown-toggle" data-toggle="dropdown">API Docs<b class="caret"></b></a>
                             <ul class="dropdown-menu">
                                 <li><a href="api/scala/index.html#org.apache.spark.package">Scala</a></li>
                                 <li><a href="api/java/index.html">Java</a></li>
                                 <li><a href="api/python/index.html">Python</a></li>
                                 <li><a href="api/R/index.html">R</a></li>
                                 <li><a href="api/sql/index.html">SQL, Built-in Functions</a></li>
                             </ul>
                         </li>

                         <li class="dropdown">
                             <a href="#" class="dropdown-toggle" data-toggle="dropdown">Deploying<b class="caret"></b></a>
                             <ul class="dropdown-menu">
                                 <li><a href="cluster-overview.html">Overview</a></li>
                                 <li><a href="submitting-applications.html">Submitting Applications</a></li>
                                 <li class="divider"></li>
                                 <li><a href="spark-standalone.html">Spark Standalone</a></li>
                                 <li><a href="running-on-mesos.html">Mesos</a></li>
                                 <li><a href="running-on-yarn.html">YARN</a></li>
                                 <li><a href="running-on-kubernetes.html">Kubernetes</a></li>
                             </ul>
                         </li>

                         <li class="dropdown">
                             <a href="api.html" class="dropdown-toggle" data-toggle="dropdown">More<b class="caret"></b></a>
                             <ul class="dropdown-menu">
                                 <li><a href="configuration.html">Configuration</a></li>
                                 <li><a href="monitoring.html">Monitoring</a></li>
                                 <li><a href="tuning.html">Tuning Guide</a></li>
                                 <li><a href="job-scheduling.html">Job Scheduling</a></li>
                                 <li><a href="security.html">Security</a></li>
                                 <li><a href="hardware-provisioning.html">Hardware Provisioning</a></li>
                                 <li class="divider"></li>
                                 <li><a href="building-spark.html">Building Spark</a></li>
                                 <li><a href="https://spark.apache.org/contributing.html">Contributing to Spark</a></li>
                                 <li><a href="https://spark.apache.org/third-party-projects.html">Third Party Projects</a></li>
                             </ul>
                         </li>
                     </ul>
                     <!--<p class="navbar-text pull-right"><span class="version-text">v2.4.0</span></p>-->
                 </div>
             </div>
         </div>

         <div class="container-wrapper">


                     <div class="left-menu-wrapper">
     <div class="left-menu">
         <h3><a href="sql-programming-guide.html">Spark SQL Guide</a></h3>

 <ul>

     <li>
         <a href="sql-getting-started.html">

                 Getting Started

         </a>
     </li>


     <li>
         <a href="sql-data-sources.html">

                 Data Sources

         </a>
     </li>


     <li>
         <a href="sql-performance-tuning.html">

                 Performance Tuning

         </a>
     </li>


     <li>
         <a href="sql-distributed-sql-engine.html">

                 Distributed SQL Engine

         </a>
     </li>


     <li>
         <a href="sql-pyspark-pandas-with-arrow.html">

                 PySpark Usage Guide for Pandas with Apache Arrow

         </a>
     </li>


     <li>
         <a href="sql-migration-guide.html">

                 Migration Guide

         </a>
     </li>


     <li>
         <a href="sql-reference.html">

                 Reference

         </a>
     </li>


 </ul>

     </div>
 </div>

                 <input id="nav-trigger" class="nav-trigger" checked type="checkbox">
                 <label for="nav-trigger"></label>
                 <div class="content-with-sidebar" id="content">

                         <h1 class="title">Spark SQL, DataFrames and Datasets Guide</h1>


                     <p>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.</p>

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

 <h2 id="sql">SQL</h2>

 <p>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 <a href="sql-data-sources-hive-tables.html">Hive Tables</a> section. When running
 SQL from within another programming language the results will be returned as a <a href="#datasets-and-dataframes">Dataset/DataFrame</a>.
 You can also interact with the SQL interface using the <a href="sql-distributed-sql-engine.html#running-the-spark-sql-cli">command-line</a>
 or over <a href="sql-distributed-sql-engine.html#running-the-thrift-jdbcodbc-server">JDBC/ODBC</a>.</p>

 <h2 id="datasets-and-dataframes">Datasets and DataFrames</h2>

 <p>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&#8217;s optimized
 execution engine. A Dataset can be <a href="sql-getting-started.html#creating-datasets">constructed</a> from JVM objects and then
 manipulated using functional transformations (<code>map</code>, <code>flatMap</code>, <code>filter</code>, etc.).
 The Dataset API is available in <a href="api/scala/index.html#org.apache.spark.sql.Dataset">Scala</a> and
 <a href="api/java/index.html?org/apache/spark/sql/Dataset.html">Java</a>. Python does not have the support for the Dataset API. But due to Python&#8217;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
 <code>row.columnName</code>). The case for R is similar.</p>

 <p>A DataFrame is a <em>Dataset</em> 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 <a href="sql-data-sources.html">sources</a> such
 as: structured data files, tables in Hive, external databases, or existing RDDs.
 The DataFrame API is available in Scala,
 Java, <a href="api/python/pyspark.sql.html#pyspark.sql.DataFrame">Python</a>, and <a href="api/R/index.html">R</a>.
 In Scala and Java, a DataFrame is represented by a Dataset of <code>Row</code>s.
 In <a href="api/scala/index.html#org.apache.spark.sql.Dataset">the Scala API</a>, <code>DataFrame</code> is simply a type alias of <code>Dataset[Row]</code>.
 While, in <a href="api/java/index.html?org/apache/spark/sql/Dataset.html">Java API</a>, users need to use <code>Dataset&lt;Row&gt;</code> to represent a <code>DataFrame</code>.</p>

 <p>Throughout this document, we will often refer to Scala/Java Datasets of <code>Row</code>s as DataFrames.</p>


                 </div>

              <!-- /container -->
         </div>

         <script src="js/vendor/jquery-1.8.0.min.js"></script>
         <script src="js/vendor/bootstrap.min.js"></script>
         <script src="js/vendor/anchor.min.js"></script>
         <script src="js/main.js"></script>

         <!-- MathJax Section -->
         <script type="text/x-mathjax-config">
             MathJax.Hub.Config({
                 TeX: { equationNumbers: { autoNumber: "AMS" } }
             });
         </script>
         <script>
             // Note that we load MathJax this way to work with local file (file://), HTTP and HTTPS.
             // We could use "//cdn.mathjax...", but that won't support "file://".
             (function(d, script) {
                 script = d.createElement('script');
                 script.type = 'text/javascript';
                 script.async = true;
                 script.onload = function(){
                     MathJax.Hub.Config({
                         tex2jax: {
                             inlineMath: [ ["$", "$"], ["\\\\(","\\\\)"] ],
                             displayMath: [ ["$$","$$"], ["\\[", "\\]"] ],
                             processEscapes: true,
                             skipTags: ['script', 'noscript', 'style', 'textarea', 'pre']
                         }
                     });
                 };
                 script.src = ('https:' == document.location.protocol ? 'https://' : 'http://') +
                     'cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js' +
                     '?config=TeX-AMS-MML_HTMLorMML';
                 d.getElementsByTagName('head')[0].appendChild(script);
             }(document));
         </script>
     </body>
 </html>

	<!DOCTYPE html>
	<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
	<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
	<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
	<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
	<head>
	<meta charset="utf-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
	<title>Spark SQL and DataFrames - Spark 2.4.0 Documentation</title>




	<link rel="stylesheet" href="css/bootstrap.min.css">
	<style>
	body {
	padding-top: 60px;
	padding-bottom: 40px;
	}
	</style>
	<meta name="viewport" content="width=device-width">
	<link rel="stylesheet" href="css/bootstrap-responsive.min.css">
	<link rel="stylesheet" href="css/main.css">

	<script src="js/vendor/modernizr-2.6.1-respond-1.1.0.min.js"></script>

	<link rel="stylesheet" href="css/pygments-default.css">


	<!-- Google analytics script -->
	<script type="text/javascript">
	var _gaq = _gaq \|\| [];
	_gaq.push(['_setAccount', 'UA-32518208-2']);
	_gaq.push(['_trackPageview']);

	(function() {
	var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
	ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
	var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
	})();
	</script>


	</head>
	<body>
	<!--[if lt IE 7]>
	<p class="chromeframe">You are using an outdated browser. <a href="https://browsehappy.com/">Upgrade your browser today</a> or <a href="http://www.google.com/chromeframe/?redirect=true">install Google Chrome Frame</a> to better experience this site.</p>
	<![endif]-->

	<!-- This code is taken from http://twitter.github.com/bootstrap/examples/hero.html -->

	<div class="navbar navbar-fixed-top" id="topbar">
	<div class="navbar-inner">
	<div class="container">
	<div class="brand"><a href="index.html">
	<img src="img/spark-logo-hd.png" style="height:50px;"/></a><span class="version">2.4.0</span>
	</div>
	<ul class="nav">
	<!--TODO(andyk): Add class="active" attribute to li some how.-->
	<li><a href="index.html">Overview</a></li>

	<li class="dropdown">
	<a href="#" class="dropdown-toggle" data-toggle="dropdown">Programming Guides<b class="caret"></b></a>
	<ul class="dropdown-menu">
	<li><a href="quick-start.html">Quick Start</a></li>
	<li><a href="rdd-programming-guide.html">RDDs, Accumulators, Broadcasts Vars</a></li>
	<li><a href="sql-programming-guide.html">SQL, DataFrames, and Datasets</a></li>
	<li><a href="structured-streaming-programming-guide.html">Structured Streaming</a></li>
	<li><a href="streaming-programming-guide.html">Spark Streaming (DStreams)</a></li>
	<li><a href="ml-guide.html">MLlib (Machine Learning)</a></li>
	<li><a href="graphx-programming-guide.html">GraphX (Graph Processing)</a></li>
	<li><a href="sparkr.html">SparkR (R on Spark)</a></li>
	</ul>
	</li>

	<li class="dropdown">
	<a href="#" class="dropdown-toggle" data-toggle="dropdown">API Docs<b class="caret"></b></a>
	<ul class="dropdown-menu">
	<li><a href="api/scala/index.html#org.apache.spark.package">Scala</a></li>
	<li><a href="api/java/index.html">Java</a></li>
	<li><a href="api/python/index.html">Python</a></li>
	<li><a href="api/R/index.html">R</a></li>
	<li><a href="api/sql/index.html">SQL, Built-in Functions</a></li>
	</ul>
	</li>

	<li class="dropdown">
	<a href="#" class="dropdown-toggle" data-toggle="dropdown">Deploying<b class="caret"></b></a>
	<ul class="dropdown-menu">
	<li><a href="cluster-overview.html">Overview</a></li>
	<li><a href="submitting-applications.html">Submitting Applications</a></li>
	<li class="divider"></li>
	<li><a href="spark-standalone.html">Spark Standalone</a></li>
	<li><a href="running-on-mesos.html">Mesos</a></li>
	<li><a href="running-on-yarn.html">YARN</a></li>
	<li><a href="running-on-kubernetes.html">Kubernetes</a></li>
	</ul>
	</li>

	<li class="dropdown">
	<a href="api.html" class="dropdown-toggle" data-toggle="dropdown">More<b class="caret"></b></a>
	<ul class="dropdown-menu">
	<li><a href="configuration.html">Configuration</a></li>
	<li><a href="monitoring.html">Monitoring</a></li>
	<li><a href="tuning.html">Tuning Guide</a></li>
	<li><a href="job-scheduling.html">Job Scheduling</a></li>
	<li><a href="security.html">Security</a></li>
	<li><a href="hardware-provisioning.html">Hardware Provisioning</a></li>
	<li class="divider"></li>
	<li><a href="building-spark.html">Building Spark</a></li>
	<li><a href="https://spark.apache.org/contributing.html">Contributing to Spark</a></li>
	<li><a href="https://spark.apache.org/third-party-projects.html">Third Party Projects</a></li>
	</ul>
	</li>
	</ul>
	<!--<p class="navbar-text pull-right"><span class="version-text">v2.4.0</span></p>-->
	</div>
	</div>
	</div>

	<div class="container-wrapper">



	<div class="left-menu-wrapper">
	<div class="left-menu">
	<h3><a href="sql-programming-guide.html">Spark SQL Guide</a></h3>

	<ul>

	<li>
	<a href="sql-getting-started.html">

	Getting Started

	</a>
	</li>



	<li>
	<a href="sql-data-sources.html">

	Data Sources

	</a>
	</li>



	<li>
	<a href="sql-performance-tuning.html">

	Performance Tuning

	</a>
	</li>



	<li>
	<a href="sql-distributed-sql-engine.html">

	Distributed SQL Engine

	</a>
	</li>



	<li>
	<a href="sql-pyspark-pandas-with-arrow.html">

	PySpark Usage Guide for Pandas with Apache Arrow

	</a>
	</li>



	<li>
	<a href="sql-migration-guide.html">

	Migration Guide

	</a>
	</li>



	<li>
	<a href="sql-reference.html">

	Reference

	</a>
	</li>



	</ul>

	</div>
	</div>

	<input id="nav-trigger" class="nav-trigger" checked type="checkbox">
	<label for="nav-trigger"></label>
	<div class="content-with-sidebar" id="content">

	<h1 class="title">Spark SQL, DataFrames and Datasets Guide</h1>


	<p>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.</p>

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

	<h2 id="sql">SQL</h2>

	<p>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 <a href="sql-data-sources-hive-tables.html">Hive Tables</a> section. When running
	SQL from within another programming language the results will be returned as a <a href="#datasets-and-dataframes">Dataset/DataFrame</a>.
	You can also interact with the SQL interface using the <a href="sql-distributed-sql-engine.html#running-the-spark-sql-cli">command-line</a>
	or over <a href="sql-distributed-sql-engine.html#running-the-thrift-jdbcodbc-server">JDBC/ODBC</a>.</p>

	<h2 id="datasets-and-dataframes">Datasets and DataFrames</h2>

	<p>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 <a href="sql-getting-started.html#creating-datasets">constructed</a> from JVM objects and then
	manipulated using functional transformations (<code>map</code>, <code>flatMap</code>, <code>filter</code>, etc.).
	The Dataset API is available in <a href="api/scala/index.html#org.apache.spark.sql.Dataset">Scala</a> and
	<a href="api/java/index.html?org/apache/spark/sql/Dataset.html">Java</a>. 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
	<code>row.columnName</code>). The case for R is similar.</p>

	<p>A DataFrame is a <em>Dataset</em> 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 <a href="sql-data-sources.html">sources</a> such
	as: structured data files, tables in Hive, external databases, or existing RDDs.
	The DataFrame API is available in Scala,
	Java, <a href="api/python/pyspark.sql.html#pyspark.sql.DataFrame">Python</a>, and <a href="api/R/index.html">R</a>.
	In Scala and Java, a DataFrame is represented by a Dataset of <code>Row</code>s.
	In <a href="api/scala/index.html#org.apache.spark.sql.Dataset">the Scala API</a>, <code>DataFrame</code> is simply a type alias of <code>Dataset[Row]</code>.
	While, in <a href="api/java/index.html?org/apache/spark/sql/Dataset.html">Java API</a>, users need to use <code>Dataset<Row></code> to represent a <code>DataFrame</code>.</p>

	<p>Throughout this document, we will often refer to Scala/Java Datasets of <code>Row</code>s as DataFrames.</p>


	</div>

	<!-- /container -->
	</div>

	<script src="js/vendor/jquery-1.8.0.min.js"></script>
	<script src="js/vendor/bootstrap.min.js"></script>
	<script src="js/vendor/anchor.min.js"></script>
	<script src="js/main.js"></script>

	<!-- MathJax Section -->
	<script type="text/x-mathjax-config">
	MathJax.Hub.Config({
	TeX: { equationNumbers: { autoNumber: "AMS" } }
	});
	</script>
	<script>
	// Note that we load MathJax this way to work with local file (file://), HTTP and HTTPS.
	// We could use "//cdn.mathjax...", but that won't support "file://".
	(function(d, script) {
	script = d.createElement('script');
	script.type = 'text/javascript';
	script.async = true;
	script.onload = function(){
	MathJax.Hub.Config({
	tex2jax: {
	inlineMath: [ ["$", "$"], ["\\\\(","\\\\)"] ],
	displayMath: [ ["$$","$$"], ["\\[", "\\]"] ],
	processEscapes: true,
	skipTags: ['script', 'noscript', 'style', 'textarea', 'pre']
	}
	});
	};
	script.src = ('https:' == document.location.protocol ? 'https://' : 'http://') +
	'cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js' +
	'?config=TeX-AMS-MML_HTMLorMML';
	d.getElementsByTagName('head')[0].appendChild(script);
	}(document));
	</script>
	</body>
	</html>