Google Git
Sign in
apache / spark-website / 2c562896505588421cce257d111c8ba3805528c2 / . / site / docs / 3.3.0 / sql-ref-syntax-dml-load.html
blob: c8a93aad8df6e53c2016bc354d9458ebe4cdbdba [file] [log] [blame]
<!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>LOAD DATA - Spark 3.3.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/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">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/docsearch.js@2/dist/cdn/docsearch.min.css" />
<link rel="stylesheet" href="css/docsearch.css">
<!-- Matomo -->
<script type="text/javascript">
var _paq = window._paq = window._paq || [];
/* tracker methods like "setCustomDimension" should be called before "trackPageView" */
_paq.push(["disableCookies"]);
_paq.push(['trackPageView']);
_paq.push(['enableLinkTracking']);
(function() {
var u="https://analytics.apache.org/";
_paq.push(['setTrackerUrl', u+'matomo.php']);
_paq.push(['setSiteId', '40']);
var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];
g.async=true; g.src=u+'matomo.js'; s.parentNode.insertBefore(g,s);
})();
</script>
<!-- End Matomo Code -->
</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 -->
<nav class="navbar fixed-top navbar-expand-md navbar-light bg-light" id="topbar">
<div class="container">
<div class="navbar-header">
<div class="navbar-brand"><a href="index.html">
<img src="img/spark-logo-hd.png" style="height:50px;"/></a><span class="version">3.3.0</span>
</div>
</div>
<button class="navbar-toggler" type="button" data-toggle="collapse"
data-target="#navbarCollapse" aria-controls="navbarCollapse"
aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarCollapse">
<ul class="navbar-nav">
<!--TODO(andyk): Add class="active" attribute to li some how.-->
<li class="nav-item"><a href="index.html" class="nav-link">Overview</a></li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarQuickStart" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Programming Guides</a>
<div class="dropdown-menu" aria-labelledby="navbarQuickStart">
<a class="dropdown-item" href="quick-start.html">Quick Start</a>
<a class="dropdown-item" href="rdd-programming-guide.html">RDDs, Accumulators, Broadcasts Vars</a>
<a class="dropdown-item" href="sql-programming-guide.html">SQL, DataFrames, and Datasets</a>
<a class="dropdown-item" href="structured-streaming-programming-guide.html">Structured Streaming</a>
<a class="dropdown-item" href="streaming-programming-guide.html">Spark Streaming (DStreams)</a>
<a class="dropdown-item" href="ml-guide.html">MLlib (Machine Learning)</a>
<a class="dropdown-item" href="graphx-programming-guide.html">GraphX (Graph Processing)</a>
<a class="dropdown-item" href="sparkr.html">SparkR (R on Spark)</a>
<a class="dropdown-item" href="api/python/getting_started/index.html">PySpark (Python on Spark)</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarAPIDocs" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">API Docs</a>
<div class="dropdown-menu" aria-labelledby="navbarAPIDocs">
<a class="dropdown-item" href="api/scala/org/apache/spark/index.html">Scala</a>
<a class="dropdown-item" href="api/java/index.html">Java</a>
<a class="dropdown-item" href="api/python/index.html">Python</a>
<a class="dropdown-item" href="api/R/index.html">R</a>
<a class="dropdown-item" href="api/sql/index.html">SQL, Built-in Functions</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarDeploying" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Deploying</a>
<div class="dropdown-menu" aria-labelledby="navbarDeploying">
<a class="dropdown-item" href="cluster-overview.html">Overview</a>
<a class="dropdown-item" href="submitting-applications.html">Submitting Applications</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="spark-standalone.html">Spark Standalone</a>
<a class="dropdown-item" href="running-on-mesos.html">Mesos</a>
<a class="dropdown-item" href="running-on-yarn.html">YARN</a>
<a class="dropdown-item" href="running-on-kubernetes.html">Kubernetes</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarMore" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">More</a>
<div class="dropdown-menu" aria-labelledby="navbarMore">
<a class="dropdown-item" href="configuration.html">Configuration</a>
<a class="dropdown-item" href="monitoring.html">Monitoring</a>
<a class="dropdown-item" href="tuning.html">Tuning Guide</a>
<a class="dropdown-item" href="job-scheduling.html">Job Scheduling</a>
<a class="dropdown-item" href="security.html">Security</a>
<a class="dropdown-item" href="hardware-provisioning.html">Hardware Provisioning</a>
<a class="dropdown-item" href="migration-guide.html">Migration Guide</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="building-spark.html">Building Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/contributing.html">Contributing to Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/third-party-projects.html">Third Party Projects</a>
</div>
</li>
<li class="nav-item">
<input type="text" id="docsearch-input" placeholder="Search the docs…">
</li>
</ul>
<!--<span class="navbar-text navbar-right"><span class="version-text">v3.3.0</span></span>-->
</div>
</div>
</nav>
<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-old.html">
Migration Guide
</a>
</li>
<li>
<a href="sql-ref.html">
SQL Reference
</a>
</li>
<ul>
<li>
<a href="sql-ref-ansi-compliance.html">
ANSI Compliance
</a>
</li>
<li>
<a href="sql-ref-datatypes.html">
Data Types
</a>
</li>
<li>
<a href="sql-ref-datetime-pattern.html">
Datetime Pattern
</a>
</li>
<li>
<a href="sql-ref-number-pattern.html">
Number Pattern
</a>
</li>
<li>
<a href="sql-ref-functions.html">
Functions
</a>
</li>
<li>
<a href="sql-ref-identifier.html">
Identifiers
</a>
</li>
<li>
<a href="sql-ref-literals.html">
Literals
</a>
</li>
<li>
<a href="sql-ref-null-semantics.html">
Null Semantics
</a>
</li>
<li>
<a href="sql-ref-syntax.html">
SQL Syntax
</a>
</li>
<ul>
<li>
<a href="sql-ref-syntax.html#ddl-statements">
Data Definition Statements
</a>
</li>
<li>
<a href="sql-ref-syntax.html#dml-statements">
Data Manipulation Statements
</a>
</li>
<li>
<a href="sql-ref-syntax.html#data-retrieval-statements">
Data Retrieval(Queries)
</a>
</li>
<li>
<a href="sql-ref-syntax.html#auxiliary-statements">
Auxiliary Statements
</a>
</li>
</ul>
</ul>
</ul>
</div>
</div>
<input id="nav-trigger" class="nav-trigger" checked type="checkbox">
<label for="nav-trigger"></label>
<div class="content-with-sidebar mr-3" id="content">
<h1 class="title">LOAD DATA</h1>
<h3 id="description">Description</h3>
<p><code class="language-plaintext highlighter-rouge">LOAD DATA</code> statement loads the data into a Hive serde table from the user specified directory or file. If a directory is specified then all the files from the directory are loaded. If a file is specified then only the single file is loaded. Additionally the <code class="language-plaintext highlighter-rouge">LOAD DATA</code> statement takes an optional partition specification. When a partition is specified, the data files (when input source is a directory) or the single file (when input source is a file) are loaded into the partition of the target table.</p>
<p>If the table is cached, the command clears cached data of the table and all its dependents that refer to it. The cache will be lazily filled when the next time the table or the dependents are accessed.</p>
<h3 id="syntax">Syntax</h3>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">LOAD</span> <span class="k">DATA</span> <span class="p">[</span> <span class="k">LOCAL</span> <span class="p">]</span> <span class="n">INPATH</span> <span class="n">path</span> <span class="p">[</span> <span class="n">OVERWRITE</span> <span class="p">]</span> <span class="k">INTO</span> <span class="k">TABLE</span> <span class="n">table_identifier</span> <span class="p">[</span> <span class="n">partition_spec</span> <span class="p">]</span>
</code></pre></div></div>
<h3 id="parameters">Parameters</h3>
<ul>
<li>
<p><strong>path</strong></p>
<p>Path of the file system. It can be either an absolute or a relative path.</p>
</li>
<li>
<p><strong>table_identifier</strong></p>
<p>Specifies a table name, which may be optionally qualified with a database name.</p>
<p><strong>Syntax:</strong> <code class="language-plaintext highlighter-rouge">[ database_name. ] table_name</code></p>
</li>
<li>
<p><strong>partition_spec</strong></p>
<p>An optional parameter that specifies a comma separated list of key and value pairs
for partitions.</p>
<p><strong>Syntax:</strong> <code class="language-plaintext highlighter-rouge">PARTITION ( partition_col_name = partition_col_val [ , ... ] )</code></p>
</li>
<li>
<p><strong>LOCAL</strong></p>
<p>If specified, it causes the <code class="language-plaintext highlighter-rouge">INPATH</code> to be resolved against the local file system, instead of the default file system, which is typically a distributed storage.</p>
</li>
<li>
<p><strong>OVERWRITE</strong></p>
<p>By default, new data is appended to the table. If <code class="language-plaintext highlighter-rouge">OVERWRITE</code> is used, the table is instead overwritten with new data.</p>
</li>
</ul>
<h3 id="examples">Examples</h3>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Example without partition specification.</span>
<span class="c1">-- Assuming the students table has already been created and populated.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">students</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span><span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span><span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">test_load</span> <span class="p">(</span><span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">64</span><span class="p">),</span> <span class="n">address</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">64</span><span class="p">),</span> <span class="n">student_id</span> <span class="nb">INT</span><span class="p">)</span> <span class="k">USING</span> <span class="n">HIVE</span><span class="p">;</span>
<span class="c1">-- Assuming the students table is in '/user/hive/warehouse/'</span>
<span class="k">LOAD</span> <span class="k">DATA</span> <span class="k">LOCAL</span> <span class="n">INPATH</span> <span class="s1">'/user/hive/warehouse/students'</span> <span class="n">OVERWRITE</span> <span class="k">INTO</span> <span class="k">TABLE</span> <span class="n">test_load</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test_load</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span> <span class="n">name</span><span class="o">|</span> <span class="n">address</span><span class="o">|</span><span class="n">student_id</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="o">|</span><span class="n">Amy</span> <span class="n">Smith</span><span class="o">|</span><span class="mi">123</span> <span class="n">Park</span> <span class="n">Ave</span><span class="p">,</span> <span class="n">San</span> <span class="n">Jose</span><span class="o">|</span> <span class="mi">111111</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+----------------------+----------+</span>
<span class="c1">-- Example with partition specification.</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">test_partition</span> <span class="p">(</span><span class="n">c1</span> <span class="nb">INT</span><span class="p">,</span> <span class="n">c2</span> <span class="nb">INT</span><span class="p">,</span> <span class="n">c3</span> <span class="nb">INT</span><span class="p">)</span> <span class="n">PARTITIONED</span> <span class="k">BY</span> <span class="p">(</span><span class="n">c2</span><span class="p">,</span> <span class="n">c3</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">test_partition</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">c2</span> <span class="o">=</span> <span class="mi">2</span><span class="p">,</span> <span class="n">c3</span> <span class="o">=</span> <span class="mi">3</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mi">1</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">test_partition</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">c2</span> <span class="o">=</span> <span class="mi">5</span><span class="p">,</span> <span class="n">c3</span> <span class="o">=</span> <span class="mi">6</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mi">4</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">test_partition</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">c2</span> <span class="o">=</span> <span class="mi">8</span><span class="p">,</span> <span class="n">c3</span> <span class="o">=</span> <span class="mi">9</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mi">7</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test_partition</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+---+---+</span>
<span class="o">|</span> <span class="n">c1</span><span class="o">|</span> <span class="n">c2</span><span class="o">|</span> <span class="n">c3</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+---+---+</span>
<span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span>
<span class="o">|</span> <span class="mi">4</span><span class="o">|</span> <span class="mi">5</span><span class="o">|</span> <span class="mi">6</span><span class="o">|</span>
<span class="o">|</span> <span class="mi">7</span><span class="o">|</span> <span class="mi">8</span><span class="o">|</span> <span class="mi">9</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+---+---+</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">test_load_partition</span> <span class="p">(</span><span class="n">c1</span> <span class="nb">INT</span><span class="p">,</span> <span class="n">c2</span> <span class="nb">INT</span><span class="p">,</span> <span class="n">c3</span> <span class="nb">INT</span><span class="p">)</span> <span class="k">USING</span> <span class="n">HIVE</span> <span class="n">PARTITIONED</span> <span class="k">BY</span> <span class="p">(</span><span class="n">c2</span><span class="p">,</span> <span class="n">c3</span><span class="p">);</span>
<span class="c1">-- Assuming the test_partition table is in '/user/hive/warehouse/'</span>
<span class="k">LOAD</span> <span class="k">DATA</span> <span class="k">LOCAL</span> <span class="n">INPATH</span> <span class="s1">'/user/hive/warehouse/test_partition/c2=2/c3=3'</span>
<span class="n">OVERWRITE</span> <span class="k">INTO</span> <span class="k">TABLE</span> <span class="n">test_load_partition</span> <span class="n">PARTITION</span> <span class="p">(</span><span class="n">c2</span><span class="o">=</span><span class="mi">2</span><span class="p">,</span> <span class="n">c3</span><span class="o">=</span><span class="mi">3</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">test_load_partition</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+---+---+</span>
<span class="o">|</span> <span class="n">c1</span><span class="o">|</span> <span class="n">c2</span><span class="o">|</span> <span class="n">c3</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+---+---+</span>
<span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+---+---+</span>
</code></pre></div></div>
</div>
<!-- /container -->
</div>
<script src="js/vendor/jquery-3.5.1.min.js"></script>
<script src="js/vendor/bootstrap.bundle.min.js"></script>
<script src="js/vendor/anchor.min.js"></script>
<script src="js/main.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/docsearch.js@2/dist/cdn/docsearch.min.js"></script>
<script type="text/javascript">
// DocSearch is entirely free and automated. DocSearch is built in two parts:
// 1. a crawler which we run on our own infrastructure every 24 hours. It follows every link
// in your website and extract content from every page it traverses. It then pushes this
// content to an Algolia index.
// 2. a JavaScript snippet to be inserted in your website that will bind this Algolia index
// to your search input and display its results in a dropdown UI. If you want to find more
// details on how works DocSearch, check the docs of DocSearch.
docsearch({
apiKey: 'd62f962a82bc9abb53471cb7b89da35e',
appId: 'RAI69RXRSK',
indexName: 'apache_spark',
inputSelector: '#docsearch-input',
enhancedSearchInput: true,
algoliaOptions: {
'facetFilters': ["version:3.3.0"]
},
debug: false // Set debug to true if you want to inspect the dropdown
});
</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>