yexinru/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
Files
taurusdb
doc-exports/docs/dli/sqlreference/dli_08_0095.html
Su, Xiaomeng be9eabe464 dli_sqlreference_20250305 
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: Su, Xiaomeng <suxiaomeng1@huawei.com>
Co-committed-by: Su, Xiaomeng <suxiaomeng1@huawei.com>
2025-03-25 09:06:21 +00:00

111 lines
20 KiB
HTML
Raw Permalink Blame History

<a name="dli_08_0095"></a><a name="dli_08_0095"></a>
<h1 class="topictitle1">Inserting Data</h1>
<div id="body8662426"><div class="section" id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_s9964bb020c52461992d2c7711fd7feed"><h4 class="sectiontitle">Function</h4><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_a453a1e6d2a6e4b05a42b498a72d224bc">This statement is used to insert the SELECT query result or a certain data record into a table.</p>
</div>
<div class="section" id="dli_08_0095__section1480717121549"><h4 class="sectiontitle">Notes and Constraints</h4><ul id="dli_08_0095__ul1282125143915"><li id="dli_08_0095__li8821525103916">The <strong id="dli_08_0095__b19412132395515">INSERT OVERWRITE</strong> syntax is not suitable for "read-write" scenarios, where data is continuously processed and updated. Using this syntax in such scenarios may result in data loss.<p id="dli_08_0095__p127271127173912">"Read-write" refers to the ability to read data while generating new data or modifying existing data during data processing.</p>
</li><li id="dli_08_0095__li181767227540">When using Hive and Datasource tables (excluding Hudi), executing data modification commands (such as <strong id="dli_08_0095__b236110285217">insert into</strong> and <strong id="dli_08_0095__b1467713331723">load data</strong>) may result in data duplication or inconsistency if the data source does not support transactions and there is a system failure or queue restart.<p id="dli_08_0095__p15597549191616">To avoid this situation, you are advised to prioritize data sources that support transactions, such as Hudi data sources. This type of data source has Atomicity, Consistency, Isolation, Durability (ACID) capabilities, which helps ensure data consistency and accuracy.</p>
<p id="dli_08_0095__p1542252311541">To learn more, refer to <a href="#dli_08_0095__section1516329541">How Do I Handle Duplicate Records After Executing the INSERT INTO Statement?</a></p>
</li></ul>
</div>
<div class="section" id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_s13bf56fb20de4983b60dc6ad83bb2dd6"><h4 class="sectiontitle">Syntax</h4><ul id="dli_08_0095__ul13867165518517"><li id="dli_08_0095__li158671552516">Insert the SELECT query result into a table.<div class="codecoloring" codetype="Sql" id="dli_08_0095__en-us_topic_0114776189_screen16540185581420"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="p">[</span><span class="k">TABLE</span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="n">db_name</span><span class="p">.]</span><span class="k">table_name</span>
<span class="w"> </span><span class="p">[</span><span class="n">PARTITION</span><span class="w"> </span><span class="n">part_spec</span><span class="p">]</span><span class="w"> </span><span class="n">select_statement</span><span class="p">;</span>
</pre></div></td></tr></table></div>
</div>
<div class="codecoloring" codetype="Sql" id="dli_08_0095__en-us_topic_0114776189_screen1111303213273"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">INSERT</span><span class="w"> </span><span class="n">OVERWRITE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="p">[</span><span class="n">db_name</span><span class="p">.]</span><span class="k">table_name</span>
<span class="w"> </span><span class="p">[</span><span class="n">PARTITION</span><span class="w"> </span><span class="n">part_spec</span><span class="p">]</span><span class="w"> </span><span class="n">select_statement</span><span class="p">;</span>
</pre></div></td></tr></table></div>
</div>
<pre class="screen" id="dli_08_0095__screen1032361515318">part_spec:
: (part_col_name1=val1 [, part_col_name2=val2, ...])</pre>
</li><li id="dli_08_0095__li1618199185320">Insert a data record into a table.<div class="codecoloring" codetype="Sql" id="dli_08_0095__screen118119925311"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="p">[</span><span class="k">TABLE</span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="n">db_name</span><span class="p">.]</span><span class="k">table_name</span>
<span class="w"> </span><span class="p">[</span><span class="n">PARTITION</span><span class="w"> </span><span class="n">part_spec</span><span class="p">]</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="n">values_row</span><span class="w"> </span><span class="p">[,</span><span class="w"> </span><span class="n">values_row</span><span class="w"> </span><span class="p">...];</span>
</pre></div></td></tr></table></div>
</div>
<div class="codecoloring" codetype="Sql" id="dli_08_0095__screen11822093536"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">INSERT</span><span class="w"> </span><span class="n">OVERWRITE</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="p">[</span><span class="n">db_name</span><span class="p">.]</span><span class="k">table_name</span>
<span class="w"> </span><span class="p">[</span><span class="n">PARTITION</span><span class="w"> </span><span class="n">part_spec</span><span class="p">]</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="n">values_row</span><span class="w"> </span><span class="p">[,</span><span class="w"> </span><span class="n">values_row</span><span class="w"> </span><span class="p">...];</span>
</pre></div></td></tr></table></div>
</div>
<pre class="screen" id="dli_08_0095__screen718211985317">values_row:
: (val1 [, val2, ...])</pre>
</li></ul>
</div>
<div class="section" id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_sc899843a7d8e47fc8a0afeb9b1287b4a"><h4 class="sectiontitle">Keywords</h4>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_td6f691783cf344f2a5d66ba9d71b7fa3" frame="border" border="1" rules="all"><caption><b>Table 1 </b>INSERT keywords</caption><thead align="left"><tr id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_r2d76bd70763d404a8928220f4b186ea7"><th align="left" class="cellrowborder" valign="top" width="18.509999999999998%" id="mcps1.3.4.2.2.3.1.1"><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_a179ef9327ac44f1fa34c44e3924e9b91"><strong id="dli_08_0095__b1547616419194">Parameter</strong></p>
</th>
<th align="left" class="cellrowborder" valign="top" width="81.49%" id="mcps1.3.4.2.2.3.1.2"><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_a9e03f595a7cb429fa8d66f2743475f0d"><strong id="dli_08_0095__en-us_topic_0093946771_en-us_topic_0053447306_en-us_topic_0039551470_b6335010717346">Description</strong></p>
</th>
</tr>
</thead>
<tbody><tr id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_row4929420420012"><td class="cellrowborder" valign="top" width="18.509999999999998%" headers="mcps1.3.4.2.2.3.1.1 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_p4099465920012">db_name</p>
</td>
<td class="cellrowborder" valign="top" width="81.49%" headers="mcps1.3.4.2.2.3.1.2 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_p3223306920012">Name of the database where the target table resides.</p>
</td>
</tr>
<tr id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_r588c66895c4d41d8ac69a0ebef8cc058"><td class="cellrowborder" valign="top" width="18.509999999999998%" headers="mcps1.3.4.2.2.3.1.1 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_ad98aed1b98714731912a1a2e13e70b29">table_name</p>
</td>
<td class="cellrowborder" valign="top" width="81.49%" headers="mcps1.3.4.2.2.3.1.2 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_ae3effa8447324943a5b79e2571c23121">Name of the target table.</p>
</td>
</tr>
<tr id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_row2138270011958"><td class="cellrowborder" valign="top" width="18.509999999999998%" headers="mcps1.3.4.2.2.3.1.1 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_p5822657411958">part_spec</p>
</td>
<td class="cellrowborder" valign="top" width="81.49%" headers="mcps1.3.4.2.2.3.1.2 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_p1873209211958">Detailed partition information. If there are multiple partition fields, all fields must be contained, but the corresponding values are optional. The system matches the corresponding partition. A maximum of 100,000 partitions can be created in a single table.</p>
</td>
</tr>
<tr id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_r7449ef2bc1454983b09e9dc791aada4f"><td class="cellrowborder" valign="top" width="18.509999999999998%" headers="mcps1.3.4.2.2.3.1.1 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_ae13b7cccd28049ce8cff0cd209a744a1">select_statement</p>
</td>
<td class="cellrowborder" valign="top" width="81.49%" headers="mcps1.3.4.2.2.3.1.2 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_a3fd3c9a93c78418ebed7437b0c871843">SELECT query on the source table (DLI and OBS tables).</p>
</td>
</tr>
<tr id="dli_08_0095__row20991412570"><td class="cellrowborder" valign="top" width="18.509999999999998%" headers="mcps1.3.4.2.2.3.1.1 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946824_p28775607195932">values_row</p>
</td>
<td class="cellrowborder" valign="top" width="81.49%" headers="mcps1.3.4.2.2.3.1.2 "><p id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946824_p49122796195932">Value to be inserted to a table. Use commas (,) to separate columns.</p>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section" id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_seb5a4a5f0f5a42769b7818ddbd12676a"><h4 class="sectiontitle">Precautions</h4><ul id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_u6ac1c73d4ae64c59ab8d3e3348e9f655"><li id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_l5e299814426d4aa6b0a4e95f3618e7c8">The target DLI table must exist.</li><li id="dli_08_0095__li1241939135317">If no partition needs to be specified for dynamic partitioning, place <span class="parmname" id="dli_08_0095__parmname1971219262522"><b>part_spec</b></span> in the SELECT statement as a common field.</li><li id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_li21345695182524">During creation of the target OBS table, only the folder path can be specified.</li><li id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_lb6813719e52a47a2a9fe88914a5c02ac">The source table and the target table must have the same data types and column field quantity. Otherwise, data insertion fails.</li><li id="dli_08_0095__en-us_topic_0114776189_li938915267158">You are not advised to insert data concurrently into the same table as it may result in abnormal data insertion due to concurrency conflicts.</li><li id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_lacc761ccaceb4324af6cf686ebace34f">The <strong id="dli_08_0095__b113291342132711">INSERT INTO</strong> statement is used to add the query result to the target table.</li><li id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_li35271963171641">The <strong id="dli_08_0095__b1054153592020">INSERT OVERWRITE</strong> statement is used to overwrite existing data in the source table.</li><li id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_lad0e709ed8864113a6f0ad7973bef85f">The <strong id="dli_08_0095__b86721037152515">INSERT INTO</strong> statement can be batch executed, but the <strong id="dli_08_0095__b960034772514">INSERT OVERWRITE</strong> statement can be batch executed only when data of different partitioned tables is inserted to different static partitions.</li><li id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_li47291019212640">The <strong id="dli_08_0095__b1327355512516">INSERT INTO</strong> and <strong id="dli_08_0095__b886312316263">INSERT OVERWRITE</strong> statements can be executed at the same time. However, the result is unknown.</li><li id="dli_08_0095__en-us_topic_0114776189_li1699211417225">When you insert data of the source table to the target table, you cannot import or update data of the source table.</li><li id="dli_08_0095__en-us_topic_0114776189_li65911315143917">The dynamic INSERT OVERWRITE statement of Hive partitioned tables can overwrite the involved partition data but cannot overwrite the entire table data.</li><li id="dli_08_0095__en-us_topic_0114776189_li1775915392195">To overwrite data in a specified partition of the datasource table, set <strong id="dli_08_0095__b1883874552015">dli.sql.dynamicPartitionOverwrite.enabled</strong> to <strong id="dli_08_0095__b12838144542014">true</strong> and run the <strong id="dli_08_0095__b1983864522016">insert overwrite</strong> statement. The default value of <span class="parmname" id="dli_08_0095__parmname48391345172019"><b>dli.sql.dynamicPartitionOverwrite.enabled</b></span> is <span class="parmvalue" id="dli_08_0095__parmvalue383924562012"><b>false</b></span>, indicating that data in the entire table is overwritten. The following is an example:<div class="codecoloring" codetype="Sql" id="dli_08_0095__en-us_topic_0114776189_screen8167771216"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">insert</span><span class="w"> </span><span class="n">overwrite</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="n">tb1</span><span class="w"> </span><span class="n">partition</span><span class="p">(</span><span class="n">part1</span><span class="o">=</span><span class="s1">'v1'</span><span class="p">,</span><span class="w"> </span><span class="n">part2</span><span class="o">=</span><span class="s1">'v2'</span><span class="p">)</span><span class="w"> </span><span class="k">select</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="p">...</span>
</pre></div></td></tr></table></div>
</div>
<div class="note" id="dli_08_0095__en-us_topic_0114776189_note8237536124016"><img src="public_sys-resources/note_3.0-en-us.png"><span class="notetitle"> </span><div class="notebody"><p id="dli_08_0095__en-us_topic_0114776189_p2024710369409">On the DLI management console, click <span class="wintitle" id="dli_08_0095__wintitle14615165112013"><b>SQL Editor</b></span>. In the upper right corner of the editing window, click <strong id="dli_08_0095__b4941647610">Settings</strong> to configure parameters.</p>
</div></div>
</li><li id="dli_08_0095__li746814001717">You can configure the <span class="parmname" id="dli_08_0095__parmname91697418568"><b>spark.sql.shuffle.partitions</b></span> parameter to set the number of files to be inserted into the OBS bucket in the non-DLI table. In addition, to avoid data skew, you can add <strong id="dli_08_0095__b1517318455620">distribute by rand()</strong> to the end of the INSERT statement to increase the number of concurrent jobs. The following is an example:<pre class="screen" id="dli_08_0095__screen14809114794213">insert into table table_target select * from table_source distribute by cast(rand() * N as int);</pre>
</li></ul>
</div>
<div class="section" id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_sc650d4ee825647c2a760ac5fcc4ab2c8"><h4 class="sectiontitle">Example</h4><div class="note" id="dli_08_0095__note1149934611312"><img src="public_sys-resources/note_3.0-en-us.png"><span class="notetitle"> </span><div class="notebody"><p id="dli_08_0095__p161225010319">Before importing data, you must create a table. For details, see <a href="dli_08_0223.html">Creating an OBS Table</a> or <a href="dli_08_0224.html">Creating a DLI Table</a>.</p>
</div></div>
<ul id="dli_08_0095__en-us_topic_0114776189_en-us_topic_0093946901_ul18471028173914"><li id="dli_08_0095__li193703191708">Insert the SELECT query result into a table.<ul id="dli_08_0095__ul4308115011213"><li id="dli_08_0095__li1494424416214">Use the DataSource syntax to create a parquet partitioned table.<pre class="screen" id="dli_08_0095__screen20881115535">CREATE TABLE data_source_tab1 (col1 INT, p1 INT, p2 INT)
USING PARQUET PARTITIONED BY (p1, p2);</pre>
</li><li id="dli_08_0095__li136969131212">Insert the query result to the partition (p1 = 3, p2 = 4).<pre class="screen" id="dli_08_0095__screen09951301751">INSERT INTO data_source_tab1 PARTITION (p1 = 3, p2 = 4)
SELECT id FROM RANGE(1, 3);</pre>
</li><li id="dli_08_0095__li28072014151212">Insert the new query result to the partition (p1 = 3, p2 = 4).<pre class="screen" id="dli_08_0095__screen623634219616">INSERT OVERWRITE TABLE data_source_tab1 PARTITION (p1 = 3, p2 = 4)
SELECT id FROM RANGE(3, 5);</pre>
</li></ul>
</li><li id="dli_08_0095__li1928215175112">Insert a data record into a table.<ul id="dli_08_0095__ul9733124541111"><li id="dli_08_0095__li10508854201111">Create a Parquet partitioned table with Hive format<pre class="screen" id="dli_08_0095__screen11281528191410">CREATE TABLE hive_serde_tab1 (col1 INT, p1 INT, p2 INT)
USING HIVE OPTIONS(fileFormat 'PARQUET') PARTITIONED BY (p1, p2);</pre>
</li><li id="dli_08_0095__li19793643111416">Insert two data records into the partition (p1 = 3, p2 = 4).<pre class="screen" id="dli_08_0095__screen13173181071519">INSERT INTO hive_serde_tab1 PARTITION (p1 = 3, p2 = 4)
VALUES (1), (2);</pre>
</li><li id="dli_08_0095__li54341043168">Insert new data to the partition (p1 = 3, p2 = 4).<pre class="screen" id="dli_08_0095__screen2731450191617">INSERT OVERWRITE TABLE hive_serde_tab1 PARTITION (p1 = 3, p2 = 4)
VALUES (3), (4);</pre>
</li></ul>
</li></ul>
</div>
<div class="section" id="dli_08_0095__section1516329541"><a name="dli_08_0095__section1516329541"></a><a name="section1516329541"></a><h4 class="sectiontitle">How Do I Handle Duplicate Records After Executing the INSERT INTO Statement?</h4><ul id="dli_08_0095__ul2474142911100"><li id="dli_08_0095__li3474729151018"><strong id="dli_08_0095__b158221857101013">Symptom</strong><p id="dli_08_0095__p7161812121210">When using Hive and Datasource tables (excluding Hudi), executing data modification commands (such as <strong id="dli_08_0095__b4387144101116">insert into</strong> and <strong id="dli_08_0095__b18387547117">load data</strong>) may result in data duplication or inconsistency if the data source does not support transactions and there is a system failure or queue restart.</p>
</li><li id="dli_08_0095__li34741129101016"><strong id="dli_08_0095__b13452711117">Possible causes</strong><p id="dli_08_0095__p75158317132">If queue resources are restarted in the data commit phase, data may have been restored to a formal directory. If an <strong id="dli_08_0095__b4661101701514">insert into</strong> statement is executed and a retry is triggered after a resource restart, there is a possibility that data will be repeatedly written.</p>
</li><li id="dli_08_0095__li13474122916102"><strong id="dli_08_0095__b11522738161116">Solution</strong><ol id="dli_08_0095__ol18435046755"><li id="dli_08_0095__li94351346656">Hudi data sources that support ACID properties are recommended.</li><li id="dli_08_0095__li243524615514">Use idempotent syntax such as <strong id="dli_08_0095__b0937133713207">insert overwrite</strong> instead of non-idempotent syntax such as <strong id="dli_08_0095__b14537154652016">insert into</strong> to insert data.</li><li id="dli_08_0095__li134352463515">If it is strictly required that data cannot be duplicated, you are advised to perform deduplication on the table data after executing the <strong id="dli_08_0095__b20137134013226">insert into</strong> statement to prevent duplicate data.</li></ol>
</li></ul>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="dli_08_0221.html">Spark SQL Syntax Reference</a></div>
</div>
</div>
View Git Blame Copy Permalink