yexinru/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
Files
sgode-patch-2
doc-exports/docs/dws/dev/dws_06_0362.html
luhuayi 177cd61a57 DWS DEVG 910.211 version 
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: luhuayi <luhuayi@huawei.com>
Co-committed-by: luhuayi <luhuayi@huawei.com>
2025-05-05 07:44:03 +00:00

141 lines
21 KiB
HTML
Raw Permalink Blame History

<a name="EN-US_TOPIC_0000001811515629"></a><a name="EN-US_TOPIC_0000001811515629"></a>
<h1 class="topictitle1">Hot and Cold Table Functions</h1>
<div id="body0000001554519734"><div class="section" id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_section78612133302"><h4 class="sectiontitle">pg_obs_cold_refresh_time(table_name, time)</h4><p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p12732409111">Description: Modifies the time when cold data in a hot and cold table is migrated to OBS. The default value is 00:00 every day.</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p117315402115"><strong id="EN-US_TOPIC_0000001811515629__b65981310111219">table_name</strong> indicates the name of the hot and cold table, and the type is Name. <strong id="EN-US_TOPIC_0000001811515629__b19598131014129">time</strong> indicates the time when the data switchover task is scheduled, and the type is Time.</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p12851940311">Return value: <strong id="EN-US_TOPIC_0000001811515629__b106497159286">SUCCESS</strong>, which indicates that the time is successfully modified.</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p14937112117266">Example:</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_screen2937152122611"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span></pre></div></td><td class="code"><div><pre><span></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="n">pg_obs_cold_refresh_time</span><span class="p">(</span><span class="s1">'lifecycle_table'</span><span class="p">,</span><span class="w"> </span><span class="s1">'06:30:00'</span><span class="p">);</span>
<span class="n">pg_obs_cold_refresh_time</span>
<span class="c1">--------------------------</span>
<span class="w"> </span><span class="n">SUCCESS</span>
<span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_section13322172611308"><h4 class="sectiontitle">pg_refresh_storage()</h4><p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p1286840519">Description: Switches hot data to cold data on all hot and cold tables (in OBS).</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p138619401012">Return type: int</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p13861740614">Columns in the returned value:</p>
<ul id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_ul28615406118"><li id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_li148654015119"><strong id="EN-US_TOPIC_0000001811515629__b1268516416306">success_count int</strong>: indicates the number of tables that are successfully switched.</li><li id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_li17871240312"><strong id="EN-US_TOPIC_0000001811515629__b97750712307">failed_count int</strong>: indicates the number of tables that fail to be switched.</li></ul>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p19601638132314">Example:</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_screen13601123832316"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span></pre></div></td><td class="code"><div><pre><span></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="n">pg_refresh_storage</span><span class="p">();</span>
<span class="w"> </span><span class="n">success_count</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">failed_count</span>
<span class="c1">---------------+--------------</span>
<span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">0</span>
<span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_section15517162412388"><h4 class="sectiontitle">pg_lifecycle_table_data_distribute(table_name)</h4><p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p451712443811">Description: Views the data distribution of a cold and hot table.</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p13517024173816"><strong id="EN-US_TOPIC_0000001811515629__b232739755103727">table_name</strong> indicates the table name and cannot be left blank.</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p5517924203814">Return value: record</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p89221921174416">Example: Multiple records are generated based on the number of nodes. The following example shows the data distribution in the <strong id="EN-US_TOPIC_0000001811515629__b1381331036103727">w1</strong> table when there is only one DN node.</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_screen292213211449"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span></pre></div></td><td class="code"><div><pre><span></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="n">pg_catalog</span><span class="p">.</span><span class="n">pg_lifecycle_table_data_distribute</span><span class="p">(</span><span class="s1">'w1'</span><span class="p">);</span>
<span class="w"> </span><span class="n">schemaname</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">tablename</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">nodename</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">hotpartition</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">coldpartition</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">switchablepartition</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">hotdatasize</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">colddatasize</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">switchabledatasize</span><span class="w"> </span>
<span class="c1">------------+-----------+----------+--------------+---------------+---------------------+-------------+--------------+--------------------</span>
<span class="w"> </span><span class="k">public</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">w1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">dn_1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">p2</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">p1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">80</span><span class="w"> </span><span class="n">KB</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="n">bytes</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="n">bytes</span>
<span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_section883111803917"><h4 class="sectiontitle">pg_lifecycle_node_data_distribute()</h4><p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p1293193010556">Description: Views the data distribution of all hot and cold tables.</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p683214813391">Return value: record</p>
<p id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_p49891132104015">Example: There are two cold and hot tables in the database. The data distribution is as follows:</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001811515629__en-us_topic_0000001622401169_screen18989632154014"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span>
<span class="normal">6</span></pre></div></td><td class="code"><div><pre><span></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="n">pg_catalog</span><span class="p">.</span><span class="n">pg_lifecycle_node_data_distribute</span><span class="p">();</span>
<span class="w"> </span><span class="n">schemaname</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">tablename</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">nodename</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">hotpartition</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">coldpartition</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">switchablepartition</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">hotdatasize</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">colddatasize</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">switchabledatasize</span><span class="w"> </span>
<span class="c1">------------+-----------+----------+--------------+---------------+---------------------+-------------+--------------+--------------------</span>
<span class="w"> </span><span class="k">public</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">w1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">dn_1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">p2</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">p1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">81920</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">0</span>
<span class="w"> </span><span class="k">public</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">w2</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">dn_1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">p2</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">p1</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">81920</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="mi">0</span>
<span class="p">(</span><span class="mi">2</span><span class="w"> </span><span class="k">rows</span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001811515629__section20405153016314"><h4 class="sectiontitle">refresh_hot_storage(relname text)</h4><p id="EN-US_TOPIC_0000001811515629__p1127681912415">Description: Flushes all partition data of a specified cold and hot table to OBS. The returned value is the number of switched partitions. This function is supported only in 8.2.1.100 or later.</p>
<p id="EN-US_TOPIC_0000001811515629__p19345859181517">Parameter:</p>
<p id="EN-US_TOPIC_0000001811515629__p131546509393"><strong id="EN-US_TOPIC_0000001811515629__b1842413811413">relname</strong>: table name (name of a cold and hot table. If the name of a non-cold and hot table is used, no error is reported and <strong id="EN-US_TOPIC_0000001811515629__b94248811149">0</strong> is returned.)</p>
<p id="EN-US_TOPIC_0000001811515629__p1177261812919">Return type: integer</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001811515629__screen1491510624910"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">refresh_hot_storage</span><span class="p">(</span><span class="s1">'multi_temper_table'</span><span class="p">);</span>
<span class="w"> </span><span class="n">refresh_hot_storage</span>
<span class="c1">---------------------</span>
<span class="w"> </span><span class="mi">4</span>
<span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001811515629__section1868613301447"><h4 class="sectiontitle">refresh_hot_storage(relname text, partname text)</h4><p id="EN-US_TOPIC_0000001811515629__p86862301041">Description: Synchronizes partition data of a specified hot and cold table to OBS. The returned value is the number of switched partitions. This function is supported only in 8.2.1.100 or later.</p>
<p id="EN-US_TOPIC_0000001811515629__p3686113019417">Parameter:</p>
<ul id="EN-US_TOPIC_0000001811515629__ul9686163018416"><li id="EN-US_TOPIC_0000001811515629__li86862301842"><strong id="EN-US_TOPIC_0000001811515629__b15526126144">relname</strong>: table name (name of a cold and hot table. If the name of a non-cold and hot table is used, no error is reported and <strong id="EN-US_TOPIC_0000001811515629__b1952151217142">0</strong> is returned.)</li><li id="EN-US_TOPIC_0000001811515629__li26863307417"><strong id="EN-US_TOPIC_0000001811515629__b0143215121411">partname</strong>: partition name (specifies a partition name of a cold and hot table)</li></ul>
<p id="EN-US_TOPIC_0000001811515629__p17686430645">Return type: integer</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001811515629__screen76866301410"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">refresh_hot_storage</span><span class="p">(</span><span class="s1">'multi_temper_table'</span><span class="p">,</span><span class="s1">'p1'</span><span class="p">);</span>
<span class="w"> </span><span class="n">refresh_hot_storage</span>
<span class="c1">---------------------</span>
<span class="w"> </span><span class="mi">1</span>
<span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001811515629__section320714553411"><h4 class="sectiontitle">reload_cold_partition(relname text)</h4><p id="EN-US_TOPIC_0000001811515629__p102079551841">Description: This function converts all cold partitions to hot ones. The returned value is the number of switched partitions. This function is supported only by 8.3.0 and later versions.</p>
<p id="EN-US_TOPIC_0000001811515629__p72078551844">Parameter:</p>
<p id="EN-US_TOPIC_0000001811515629__p12075555414"><strong id="EN-US_TOPIC_0000001811515629__b959191816146">relname</strong>: table name (name of a cold and hot table. If the name of a non-cold and hot table is used, no error is reported and <strong id="EN-US_TOPIC_0000001811515629__b260161851416">0</strong> is returned.)</p>
<p id="EN-US_TOPIC_0000001811515629__p16207145518414">Return type: integer</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001811515629__screen18207555541"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">reload_cold_partition</span><span class="p">(</span><span class="s1">'multi_temper_table'</span><span class="p">);</span>
<span class="w"> </span><span class="n">reload_cold_partition</span>
<span class="c1">---------------------</span>
<span class="w"> </span><span class="mi">4</span>
<span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001811515629__section11207175511411"><h4 class="sectiontitle">reload_cold_partition(relname text, partname text)</h4><p id="EN-US_TOPIC_0000001811515629__p13207555846">Description: This function converts specific cold partitions to hot ones. The returned value is the number of switched partitions. This function is supported only by 8.3.0 and later versions.</p>
<p id="EN-US_TOPIC_0000001811515629__p102071556413">Parameter:</p>
<ul id="EN-US_TOPIC_0000001811515629__ul22078551740"><li id="EN-US_TOPIC_0000001811515629__li620718558418"><strong id="EN-US_TOPIC_0000001811515629__b270172191418">relname</strong>: table name (name of a cold and hot table. If the name of a non-cold and hot table is used, no error is reported and <strong id="EN-US_TOPIC_0000001811515629__b7716215144">0</strong> is returned.)</li><li id="EN-US_TOPIC_0000001811515629__li192071355947"><strong id="EN-US_TOPIC_0000001811515629__b437842410147">partname</strong>: partition name (specifies a partition name of a cold and hot table)</li></ul>
<p id="EN-US_TOPIC_0000001811515629__p1620710551449">Return type: integer</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001811515629__screen1820718554416"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">SELECT</span><span class="w"> </span><span class="n">reload_cold_partition</span><span class="p">(</span><span class="s1">'multi_temper_table'</span><span class="p">,</span><span class="s1">'p1'</span><span class="p">);</span>
<span class="w"> </span><span class="n">reload_cold_partition</span>
<span class="c1">---------------------</span>
<span class="w"> </span><span class="mi">1</span>
<span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="dws_06_0058.html">Database Object Functions</a></div>
</div>
</div>
View Git Blame Copy Permalink