1"use strict";(self.webpackChunkstarrocks=self.webpackChunkstarrocks||[]).push([[2184],{28453:(e,n,t)=>{t.d(n,{R:()=>i,x:()=>d});var r=t(96540);let a={},s=r.createContext(a);function i(e){let n=r.useContext(s);return r.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function d(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(a):e.components||a:i(e.components),r.createElement(s.Provider,{value:n},e.children)}},97214:(e,n,t)=>{t.r(n),t.d(n,{assets:()=>l,contentTitle:()=>d,default:()=>E,frontMatter:()=>i,metadata:()=>r,toc:()=>o});let r=JSON.parse('{"id":"sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_LIKE","title":"CREATE TABLE LIKE","description":"CREATE TABLE LIKE creates an identical empty table based on the definition of another table. The definition includes column definition, partitions, and table properties. You can copy an external table such as MySQL.","source":"@site/versioned_docs/version-3.5/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_LIKE.md","sourceDirName":"sql-reference/sql-statements/table_bucket_part_index","slug":"/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_LIKE","permalink":"/docs/3.5/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_LIKE","draft":false,"unlisted":false,"editUrl":"https://github.com/StarRocks/starrocks/edit/main/docs/en/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_LIKE.md","tags":[],"version":"3.5","frontMatter":{"displayed_sidebar":"docs"},"sidebar":"docs","previous":{"title":"CREATE TABLE AS SELECT","permalink":"/docs/3.5/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_AS_SELECT"},"next":{"title":"DELETE","permalink":"/docs/3.5/sql-reference/sql-statements/table_bucket_part_index/DELETE"}}');var a=t(74848),s=t(28453);let i={displayed_sidebar:"docs"},d="CREATE TABLE LIKE",l={},o=[{value:"Syntax",id:"syntax",level:2},{value:"Parameters",id:"parameters",level:2},{value:"Examples",id:"examples",level:2}];function c(e){let n={a:"a",admonition:"admonition",code:"code",h1:"h1",h2:"h2",header:"header",li:"li",p:"p",pre:"pre",ul:"ul",...(0,s.R)(),...e.components};return(0,a.jsxs)(a.Fragment,{children:[(0,a.jsx)(n.header,{children:(0,a.jsx)(n.h1,{id:"create-table-like",children:"CREATE TABLE LIKE"})}),"\n",(0,a.jsx)(n.p,{children:"CREATE TABLE LIKE creates an identical empty table based on the definition of another table. The definition includes column definition, partitions, and table properties. You can copy an external table such as MySQL."}),"\n",(0,a.jsx)(n.p,{children:"v3.2 allows you to specify a different partitioning method, bucketing method, and properties for the new table than the source table."}),"\n",(0,a.jsx)(n.admonition,{type:"tip",children:(0,a.jsxs)(n.p,{children:["This operation requires the CREATE TABLE privilege on the database in which you want to create a table, and the ",(0,a.jsx)(n.code,{children:"SELECT"})," privilege on the source table based on which to create a table."]})}),"\n",(0,a.jsx)(n.h2,{id:"syntax",children:"Syntax"}),"\n",(0,a.jsxs)(n.ul,{children:["\n",(0,a.jsx)(n.li,{children:"Syntax supported in versions earlier than v3.2."}),"\n"]}),"\n",(0,a.jsx)(n.pre,{children:(0,a.jsx)(n.code,{className:"language-sql",children:"CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database.]<table_name>\nLIKE [database.]<source_table_name>\n"})}),"\n",(0,a.jsxs)(n.ul,{children:["\n",(0,a.jsx)(n.li,{children:"v3.2 supports specifying properties for the new table."}),"\n"]}),"\n",(0,a.jsx)(n.pre,{children:(0,a.jsx)(n.code,{className:"language-sql",children:'CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [database.]<table_name>\n[partition_desc]\n[distribution_desc]\n[PROPERTIES ("key" = "value",...)]\nLIKE [database.]<source_table_name>\n'})}),"\n",(0,a.jsx)(n.h2,{id:"parameters",children:"Parameters"}),"\n",(0,a.jsxs)(n.ul,{children:["\n",(0,a.jsxs)(n.li,{children:[(0,a.jsx)(n.code,{children:"TEMPORARY"}),": Creates a temporary table. From v3.3.1, StarRocks supports creating temporary tables in the Default Catalog. For more information, see ",(0,a.jsx)(n.a,{href:"/docs/3.5/table_design/StarRocks_table_design#temporary-table",children:"Temporary Table"}),"."]}),"\n",(0,a.jsxs)(n.li,{children:[(0,a.jsx)(n.code,{children:"database"}),": the database."]}),"\n",(0,a.jsxs)(n.li,{children:[(0,a.jsx)(n.code,{children:"table_name"}),": the name of the table you want to create. For the naming conventions, see ",(0,a.jsx)(n.a,{href:"/docs/3.5/sql-reference/System_limit",children:"System limits"}),"."]}),"\n",(0,a.jsxs)(n.li,{children:[(0,a.jsx)(n.code,{children:"source_table_name"}),": the name of the source table you want to copy."]}),"\n",(0,a.jsxs)(n.li,{children:["\n",(0,a.jsxs)(n.ul,{children:["\n",(0,a.jsxs)(n.li,{children:[(0,a.jsx)(n.code,{children:"partition_desc"}),": the partitioning method. For more information, see ",(0,a.jsx)(n.a,{href:"/docs/3.5/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE#partition_desc",children:"CREATE TABLE"}),"."]}),"\n"]}),"\n"]}),"\n",(0,a.jsxs)(n.li,{children:[(0,a.jsx)(n.code,{children:"distribution_desc"}),": the bucketing method. For more information, see ",(0,a.jsx)(n.a,{href:"/docs/3.5/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE#distribution_desc",children:"CREATE TABLE"}),"."]}),"\n",(0,a.jsxs)(n.li,{children:[(0,a.jsx)(n.code,{children:"PROPERTIES"}),": the properties of the table. All the table properties are supported. For more information, see ",(0,a.jsx)(n.a,{href:"/docs/3.5/sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE#modify-table-properties",children:"ALTER TABLE"}),"."]}),"\n"]}),"\n",(0,a.jsx)(n.h2,{id:"examples",children:"Examples"}),"\n",(0,a.jsxs)(n.p,{children:["Suppose there is a table ",(0,a.jsx)(n.code,{children:"orders"})," in database ",(0,a.jsx)(n.code,{children:"test1"}),"."]}),"\n",(0,a.jsx)(n.pre,{children:(0,a.jsx)(n.code,{className:"language-sql",children:"create table orders (\n dt date NOT NULL,\n order_id bigint NOT NULL,\n user_id int NOT NULL,\n merchant_id int NOT NULL,\n good_id int NOT NULL,\n good_n
1ame string NOT NULL,\n price int NOT NULL,\n cnt int NOT NULL,\n revenue int NOT NULL,\n state tinyint NOT NULL\n) PRIMARY KEY (dt, order_id)\nPARTITION BY RANGE(`dt`) (\n PARTITION p20210820 VALUES [('2021-08-20'), ('2021-08-21')),\n PARTITION p20210821 VALUES [('2021-08-21'), ('2021-08-22')),\n PARTITION p20210929 VALUES [('2021-09-29'), ('2021-09-30')),\n PARTITION p20210930 VALUES [('2021-09-30'), ('2021-10-01'))\n) DISTRIBUTED BY HASH(order_id)\nPROPERTIES (\n \"replication_num\" = \"3\",\n \"enable_persistent_index\" = \"true\"\n);\n"})}),"\n",(0,a.jsxs)(n.p,{children:["Example 1: In database ",(0,a.jsx)(n.code,{children:"test1"}),", create an empty table ",(0,a.jsx)(n.code,{children:"order_1"})," that has the same table structure as ",(0,a.jsx)(n.code,{children:"orders"}),"."]}),"\n",(0,a.jsx)(n.pre,{children:(0,a.jsx)(n.code,{className:"language-sql",children:"CREATE TABLE test1.order_1 LIKE test1.orders;\n"})}),"\n",(0,a.jsx)(n.pre,{children:(0,a.jsx)(n.code,{className:"language-plaintext",children:'show create table order_1\\G\n*************************** 1. row ***************************\n Table: order_1\nCreate Table: CREATE TABLE `order_1` (\n `dt` date NOT NULL COMMENT "",\n `order_id` bigint(20) NOT NULL COMMENT "",\n `user_id` int(11) NOT NULL COMMENT "",\n `merchant_id` int(11) NOT NULL COMMENT "",\n `good_id` int(11) NOT NULL COMMENT "",\n `good_name` varchar(65533) NOT NULL COMMENT "",\n `price` int(11) NOT NULL COMMENT "",\n `cnt` int(11) NOT NULL COMMENT "",\n `revenue` int(11) NOT NULL COMMENT "",\n `state` tinyint(4) NOT NULL COMMENT ""\n) ENGINE=OLAP \nPRIMARY KEY(`dt`, `order_id`)\nPARTITION BY RANGE(`dt`)\n(PARTITION p20210820 VALUES [("2021-08-20"), ("2021-08-21")),\nPARTITION p20210821 VALUES [("2021-08-21"), ("2021-08-22")),\nPARTITION p20210929 VALUES [("2021-09-29"), ("2021-09-30")),\nPARTITION p20210930 VALUES [("2021-09-30"), ("2021-10-01")))\nDISTRIBUTED BY HASH(`order_id`)\nPROPERTIES (\n"replication_num" = "3",\n"in_memory" = "false",\n"enable_persistent_index" = "true",\n"replicated_storage" = "true",\n"compression" = "LZ4"\n);\n'})}),"\n",(0,a.jsxs)(n.p,{children:["Example 2: Create an empty table ",(0,a.jsx)(n.code,{children:"order_2"})," based on ",(0,a.jsx)(n.code,{children:"orders"})," and specify properties for ",(0,a.jsx)(n.code,{children:"order_2"}),"."]}),"\n",(0,a.jsx)(n.pre,{children:(0,a.jsx)(n.code,{className:"language-sql",children:'CREATE TABLE order_2\nPARTITION BY date_trunc(\'day\',dt)\nDISTRIBUTED BY hash(dt)\nPROPERTIES ("replication_num" = "1")\nLIKE orders;\n'})}),"\n",(0,a.jsx)(n.pre,{children:(0,a.jsx)(n.code,{className:"language-plaintext",children:'show create table order_2\\G\n*************************** 1. row ***************************\n Table: order_2\nCreate Table: CREATE TABLE `order_2` (\n `dt` date NOT NULL COMMENT "",\n `order_id` bigint(20) NOT NULL COMMENT "",\n `user_id` int(11) NOT NULL COMMENT "",\n `merchant_id` int(11) NOT NULL COMMENT "",\n `good_id` int(11) NOT NULL COMMENT "",\n `good_name` varchar(65533) NOT NULL COMMENT "",\n `price` int(11) NOT NULL COMMENT "",\n `cnt` int(11) NOT NULL COMMENT "",\n `revenue` int(11) NOT NULL COMMENT "",\n `state` tinyint(4) NOT NULL COMMENT ""\n) ENGINE=OLAP \nPRIMARY KEY(`dt`, `order_id`)\nPARTITION BY RANGE(date_trunc(\'day\', dt))\n()\nDISTRIBUTED BY HASH(`dt`)\nPROPERTIES (\n"replication_num" = "1",\n"in_memory" = "false",\n"enable_persistent_index" = "true",\n"replicated_storage" = "true",\n"compression" = "LZ4"\n);\n'})}),"\n",(0,a.jsxs)(n.p,{children:["Example 3: Create an empty table ",(0,a.jsx)(n.code,{children:"table2"})," that has the same table structure as MySQL external table ",(0,a.jsx)(n.code,{children:"table1"}),"."]}),"\n",(0,a.jsx)(n.pre,{children:(0,a.jsx)(n.code,{className:"language-sql",children:"CREATE TABLE test1.table2 LIKE test1.table1\n"})})]})}function E(e={}){let{wrapper:n}={...(0,s.R)(),...e.components};return n?(0,a.jsx)(n,{...e,children:(0,a.jsx)(c,{...e})}):c(e)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.