Skip to main content

query/sql/
show_create_table.rs

1// Copyright 2023 Greptime Team
2//
3// Licensed under the Apache License, Version 2.0 (the "License");
4// you may not use this file except in compliance with the License.
5// You may obtain a copy of the License at
6//
7//     http://www.apache.org/licenses/LICENSE-2.0
8//
9// Unless required by applicable law or agreed to in writing, software
10// distributed under the License is distributed on an "AS IS" BASIS,
11// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12// See the License for the specific language governing permissions and
13// limitations under the License.
14
15//! Implementation of `SHOW CREATE TABLE` statement.
16
17use std::collections::HashMap;
18
19use arrow_schema::extension::ExtensionType;
20use common_meta::SchemaOptions;
21use datatypes::extension::json::{Json2ExtensionType, parse_legacy_json2_settings};
22use datatypes::schema::{
23    COLUMN_FULLTEXT_OPT_KEY_ANALYZER, COLUMN_FULLTEXT_OPT_KEY_BACKEND,
24    COLUMN_FULLTEXT_OPT_KEY_CASE_SENSITIVE, COLUMN_FULLTEXT_OPT_KEY_FALSE_POSITIVE_RATE,
25    COLUMN_FULLTEXT_OPT_KEY_GRANULARITY, COLUMN_SKIPPING_INDEX_OPT_KEY_FALSE_POSITIVE_RATE,
26    COLUMN_SKIPPING_INDEX_OPT_KEY_GRANULARITY, COLUMN_SKIPPING_INDEX_OPT_KEY_TYPE, COMMENT_KEY,
27    ColumnDefaultConstraint, ColumnSchema, FulltextBackend, SchemaRef,
28};
29use datatypes::types::JsonFormat;
30use snafu::ResultExt;
31use sql::ast::{ColumnDef, ColumnOption, ColumnOptionDef, DataType, Expr, Ident, ObjectName};
32use sql::dialect::GreptimeDbDialect;
33use sql::parser::ParserContext;
34use sql::statements::create::{Column, ColumnExtensions, CreateTable, TableConstraint};
35use sql::statements::{self, OptionMap, concrete_data_type_to_sql_data_type};
36use store_api::metric_engine_consts::{is_metric_engine, is_metric_engine_internal_column};
37use table::metadata::{TableInfoRef, TableMeta};
38use table::requests::{
39    COMMENT_KEY as TABLE_COMMENT_KEY, FILE_TABLE_META_KEY, RELATIONAL_CONNECTION_KEY,
40    RELATIONAL_META_KEY, RELATIONAL_REMOTE_TABLE_KEY, RelationalOptions, SKIP_WAL_KEY, TTL_KEY,
41    WRITE_BUFFER_SIZE_KEY,
42};
43
44use crate::error::{
45    ConvertSqlTypeSnafu, ConvertSqlValueSnafu, GetFulltextOptionsSnafu,
46    GetSkippingIndexOptionsSnafu, Result, SqlSnafu,
47};
48
49/// Generates CREATE TABLE options from given table metadata and schema-level options.
50fn create_sql_options(table_meta: &TableMeta, schema_options: Option<SchemaOptions>) -> OptionMap {
51    let table_opts = &table_meta.options;
52    let mut options = OptionMap::default();
53    if let Some(write_buffer_size) = table_opts.write_buffer_size {
54        options.insert(
55            WRITE_BUFFER_SIZE_KEY.to_string(),
56            write_buffer_size.to_string(),
57        );
58    }
59    if let Some(ttl) = table_opts.ttl.map(|t| t.to_string()) {
60        options.insert(TTL_KEY.to_string(), ttl);
61    } else if let Some(database_ttl) = schema_options
62        .as_ref()
63        .and_then(|o| o.ttl)
64        .map(|ttl| ttl.to_string())
65    {
66        options.insert(TTL_KEY.to_string(), database_ttl);
67    };
68    for (k, v) in table_opts
69        .extra_options
70        .iter()
71        .filter(|(k, _)| k != &FILE_TABLE_META_KEY && k != &RELATIONAL_META_KEY)
72    {
73        options.insert(k.clone(), v.clone());
74    }
75    if let Some(encoded) = table_opts.extra_options.get(RELATIONAL_META_KEY)
76        && let Ok(opts) = serde_json::from_str::<RelationalOptions>(encoded)
77    {
78        options.insert(RELATIONAL_CONNECTION_KEY.to_string(), opts.connection);
79        options.insert(RELATIONAL_REMOTE_TABLE_KEY.to_string(), opts.remote_table);
80    }
81    if table_opts.skip_wal {
82        options.insert(SKIP_WAL_KEY.to_string(), true.to_string());
83    }
84    options
85}
86
87#[inline]
88fn column_option_def(option: ColumnOption) -> ColumnOptionDef {
89    ColumnOptionDef { name: None, option }
90}
91
92fn create_column(column_schema: &ColumnSchema, quote_style: char) -> Result<Column> {
93    let name = &column_schema.name;
94    let mut options = Vec::with_capacity(2);
95    let mut extensions = ColumnExtensions::default();
96
97    if column_schema.is_nullable() {
98        options.push(column_option_def(ColumnOption::Null));
99    } else {
100        options.push(column_option_def(ColumnOption::NotNull));
101    }
102
103    if let Some(c) = column_schema.default_constraint() {
104        let expr = match c {
105            ColumnDefaultConstraint::Value(v) => Expr::Value(
106                statements::value_to_sql_value(v)
107                    .with_context(|_| ConvertSqlValueSnafu { value: v.clone() })?
108                    .into(),
109            ),
110            ColumnDefaultConstraint::Function(expr) => {
111                ParserContext::parse_function(expr, &GreptimeDbDialect {}).context(SqlSnafu)?
112            }
113        };
114
115        options.push(column_option_def(ColumnOption::Default(expr)));
116    }
117
118    if let Some(c) = column_schema.metadata().get(COMMENT_KEY) {
119        options.push(column_option_def(ColumnOption::Comment(c.clone())));
120    }
121
122    if let Some(opt) = column_schema
123        .fulltext_options()
124        .context(GetFulltextOptionsSnafu)?
125        && opt.enable
126    {
127        let mut map = HashMap::from([
128            (
129                COLUMN_FULLTEXT_OPT_KEY_ANALYZER.to_string(),
130                opt.analyzer.to_string(),
131            ),
132            (
133                COLUMN_FULLTEXT_OPT_KEY_CASE_SENSITIVE.to_string(),
134                opt.case_sensitive.to_string(),
135            ),
136            (
137                COLUMN_FULLTEXT_OPT_KEY_BACKEND.to_string(),
138                opt.backend.to_string(),
139            ),
140        ]);
141        if opt.backend == FulltextBackend::Bloom {
142            map.insert(
143                COLUMN_FULLTEXT_OPT_KEY_GRANULARITY.to_string(),
144                opt.granularity.to_string(),
145            );
146            map.insert(
147                COLUMN_FULLTEXT_OPT_KEY_FALSE_POSITIVE_RATE.to_string(),
148                opt.false_positive_rate().to_string(),
149            );
150        }
151        extensions.fulltext_index_options = Some(map.into());
152    }
153
154    if let Some(opt) = column_schema
155        .skipping_index_options()
156        .context(GetSkippingIndexOptionsSnafu)?
157    {
158        let map = HashMap::from([
159            (
160                COLUMN_SKIPPING_INDEX_OPT_KEY_GRANULARITY.to_string(),
161                opt.granularity.to_string(),
162            ),
163            (
164                COLUMN_SKIPPING_INDEX_OPT_KEY_FALSE_POSITIVE_RATE.to_string(),
165                opt.false_positive_rate().to_string(),
166            ),
167            (
168                COLUMN_SKIPPING_INDEX_OPT_KEY_TYPE.to_string(),
169                opt.index_type.to_string(),
170            ),
171        ]);
172        extensions.skipping_index_options = Some(map.into());
173    }
174
175    if column_schema.is_inverted_indexed() {
176        extensions.inverted_index_options = Some(HashMap::new().into());
177    }
178
179    if let Some(opt) = column_schema.compression_options()? {
180        extensions.compression_options = Some(opt.to_options_map().into());
181    }
182
183    let mut data_type = concrete_data_type_to_sql_data_type(&column_schema.data_type)
184        .with_context(|_| ConvertSqlTypeSnafu {
185            datatype: column_schema.data_type.clone(),
186        })?;
187
188    if matches!(
189        &column_schema.data_type,
190        datatypes::data_type::ConcreteDataType::Json(json_type)
191            if matches!(json_type.format, JsonFormat::Json2(_))
192    ) {
193        data_type = DataType::Custom(ObjectName::from(vec![Ident::new("JSON2")]), vec![]);
194    }
195
196    let settings = if let Some(extension) = column_schema.extension_type::<Json2ExtensionType>()? {
197        Some(extension.metadata().json_settings().clone())
198    } else {
199        parse_legacy_json2_settings(column_schema.metadata())?
200    };
201    if let Some(settings) = settings {
202        extensions.set_json_settings(settings).context(SqlSnafu)?;
203    }
204
205    Ok(Column {
206        column_def: ColumnDef {
207            name: Ident::with_quote(quote_style, name),
208            data_type,
209            options,
210        },
211        extensions,
212    })
213}
214
215/// Returns the primary key columns for `SHOW CREATE TABLE` statement.
216///
217/// For metric engine, it will only return the primary key columns that are not internal columns.
218fn primary_key_columns_for_show_create<'a>(
219    table_meta: &'a TableMeta,
220    engine: &str,
221) -> Vec<&'a String> {
222    let is_metric_engine = is_metric_engine(engine);
223    if is_metric_engine {
224        table_meta
225            .row_key_column_names()
226            .filter(|name| !is_metric_engine_internal_column(name))
227            .collect()
228    } else {
229        table_meta.row_key_column_names().collect()
230    }
231}
232
233fn create_table_constraints(
234    engine: &str,
235    schema: &SchemaRef,
236    table_meta: &TableMeta,
237    quote_style: char,
238) -> Vec<TableConstraint> {
239    let mut constraints = Vec::with_capacity(2);
240    if let Some(timestamp_column) = schema.timestamp_column() {
241        let column_name = &timestamp_column.name;
242        constraints.push(TableConstraint::TimeIndex {
243            column: Ident::with_quote(quote_style, column_name),
244        });
245    }
246    if !table_meta.primary_key_indices.is_empty() {
247        let columns = primary_key_columns_for_show_create(table_meta, engine)
248            .into_iter()
249            .map(|name| Ident::with_quote(quote_style, name))
250            .collect();
251        constraints.push(TableConstraint::PrimaryKey { columns });
252    }
253
254    constraints
255}
256
257/// Create a CreateTable statement from table info.
258pub fn create_table_stmt(
259    table_info: &TableInfoRef,
260    schema_options: Option<SchemaOptions>,
261    quote_style: char,
262) -> Result<CreateTable> {
263    let table_meta = &table_info.meta;
264    let table_name = &table_info.name;
265    let schema = &table_info.meta.schema;
266    let is_metric_engine = is_metric_engine(&table_meta.engine);
267    let columns = schema
268        .column_schemas()
269        .iter()
270        .filter_map(|c| {
271            if is_metric_engine && is_metric_engine_internal_column(&c.name) {
272                None
273            } else {
274                Some(create_column(c, quote_style))
275            }
276        })
277        .collect::<Result<Vec<_>>>()?;
278
279    let constraints = create_table_constraints(&table_meta.engine, schema, table_meta, quote_style);
280
281    let mut options = create_sql_options(table_meta, schema_options);
282    if let Some(comment) = &table_info.desc
283        && options.get(TABLE_COMMENT_KEY).is_none()
284    {
285        options.insert(format!("'{TABLE_COMMENT_KEY}'"), comment.clone());
286    }
287
288    Ok(CreateTable {
289        if_not_exists: true,
290        table_id: table_info.ident.table_id,
291        name: ObjectName::from(vec![Ident::with_quote(quote_style, table_name)]),
292        columns,
293        engine: table_meta.engine.clone(),
294        constraints,
295        options,
296        partitions: None,
297    })
298}
299
300#[cfg(test)]
301mod tests {
302    use std::sync::Arc;
303    use std::time::Duration;
304
305    use common_time::timestamp::TimeUnit;
306    use datatypes::extension::json::JsonExtensionType;
307    use datatypes::prelude::ConcreteDataType;
308    use datatypes::schema::{FulltextOptions, Schema, SchemaRef, SkippingIndexOptions};
309    use table::metadata::*;
310    use table::requests::{
311        FILE_TABLE_FORMAT_KEY, FILE_TABLE_LOCATION_KEY, FILE_TABLE_META_KEY,
312        RELATIONAL_CONNECTION_KEY, RELATIONAL_REMOTE_TABLE_KEY, TableOptions,
313    };
314
315    use super::*;
316
317    #[test]
318    fn test_show_create_table_sql() {
319        let schema = vec![
320            ColumnSchema::new("id", ConcreteDataType::uint32_datatype(), true)
321                .with_skipping_options(SkippingIndexOptions {
322                    granularity: 4096,
323                    ..Default::default()
324                })
325                .unwrap(),
326            ColumnSchema::new("host", ConcreteDataType::string_datatype(), true)
327                .with_inverted_index(true),
328            ColumnSchema::new("cpu", ConcreteDataType::float64_datatype(), true),
329            ColumnSchema::new("disk", ConcreteDataType::float32_datatype(), true),
330            ColumnSchema::new("msg", ConcreteDataType::string_datatype(), true)
331                .with_fulltext_options(FulltextOptions {
332                    enable: true,
333                    ..Default::default()
334                })
335                .unwrap(),
336            ColumnSchema::new("embedding", ConcreteDataType::vector_datatype(4), true),
337            ColumnSchema::new(
338                "ts",
339                ConcreteDataType::timestamp_datatype(TimeUnit::Millisecond),
340                false,
341            )
342            .with_default_constraint(Some(ColumnDefaultConstraint::Function(String::from(
343                "current_timestamp()",
344            ))))
345            .unwrap()
346            .with_time_index(true),
347        ];
348
349        let table_schema = SchemaRef::new(Schema::new(schema));
350        let table_name = "system_metrics";
351        let schema_name = "public".to_string();
352        let catalog_name = "greptime".to_string();
353
354        let mut options = table::requests::TableOptions {
355            ttl: Some(Duration::from_secs(30).into()),
356            skip_wal: true,
357            ..Default::default()
358        };
359
360        let _ = options
361            .extra_options
362            .insert("compaction.type".to_string(), "twcs".to_string());
363
364        let meta = TableMetaBuilder::empty()
365            .schema(table_schema)
366            .primary_key_indices(vec![0, 1])
367            .value_indices(vec![2, 3])
368            .engine("mito".to_string())
369            .next_column_id(0)
370            .options(options)
371            .created_on(Default::default())
372            .build()
373            .unwrap();
374
375        let info = Arc::new(
376            TableInfoBuilder::default()
377                .table_id(1024)
378                .table_version(0 as TableVersion)
379                .name(table_name)
380                .schema_name(schema_name)
381                .catalog_name(catalog_name)
382                .desc(None)
383                .table_type(TableType::Base)
384                .meta(meta)
385                .build()
386                .unwrap(),
387        );
388
389        let stmt = create_table_stmt(&info, None, '"').unwrap();
390
391        let sql = format!("\n{}", stmt);
392        assert_eq!(
393            r#"
394CREATE TABLE IF NOT EXISTS "system_metrics" (
395  "id" INT UNSIGNED NULL SKIPPING INDEX WITH(false_positive_rate = '0.01', granularity = '4096', type = 'BLOOM'),
396  "host" STRING NULL INVERTED INDEX,
397  "cpu" DOUBLE NULL,
398  "disk" FLOAT NULL,
399  "msg" STRING NULL FULLTEXT INDEX WITH(analyzer = 'English', backend = 'bloom', case_sensitive = 'false', false_positive_rate = '0.01', granularity = '10240'),
400  "embedding" VECTOR(4) NULL,
401  "ts" TIMESTAMP(3) NOT NULL DEFAULT current_timestamp(),
402  TIME INDEX ("ts"),
403  PRIMARY KEY ("id", "host")
404)
405ENGINE=mito
406WITH(
407  'compaction.type' = 'twcs',
408  skip_wal = 'true',
409  ttl = '30s'
410)"#,
411            sql
412        );
413
414        let mut table_meta = info.meta.clone();
415        table_meta.options.skip_wal = false;
416        table_meta
417            .options
418            .extra_options
419            .insert(SKIP_WAL_KEY.to_string(), false.to_string());
420        assert_eq!(
421            Some("false"),
422            create_sql_options(&table_meta, None).get(SKIP_WAL_KEY)
423        );
424
425        let mut schema_options = SchemaOptions::default();
426        schema_options
427            .extra_options
428            .insert(SKIP_WAL_KEY.to_string(), true.to_string());
429        assert_eq!(
430            Some("false"),
431            create_sql_options(&table_meta, Some(schema_options)).get(SKIP_WAL_KEY)
432        );
433    }
434
435    #[test]
436    fn test_show_create_legacy_json_with_json_extension() {
437        let mut json_column = ColumnSchema::new("j", ConcreteDataType::json_datatype(), true);
438        json_column.with_extension_type(&JsonExtensionType);
439
440        let table_schema = SchemaRef::new(Schema::new(vec![
441            json_column,
442            ColumnSchema::new(
443                "ts",
444                ConcreteDataType::timestamp_datatype(TimeUnit::Millisecond),
445                false,
446            )
447            .with_time_index(true),
448        ]));
449        let table_name = "legacy_json";
450        let meta = TableMetaBuilder::empty()
451            .schema(table_schema)
452            .primary_key_indices(vec![])
453            .value_indices(vec![0])
454            .engine("mito".to_string())
455            .next_column_id(0)
456            .options(Default::default())
457            .created_on(Default::default())
458            .build()
459            .unwrap();
460
461        let info = Arc::new(
462            TableInfoBuilder::default()
463                .table_id(1024)
464                .table_version(0 as TableVersion)
465                .name(table_name)
466                .schema_name("public")
467                .catalog_name("greptime")
468                .desc(None)
469                .table_type(TableType::Base)
470                .meta(meta)
471                .build()
472                .unwrap(),
473        );
474
475        let stmt = create_table_stmt(&info, None, '"').unwrap();
476        let sql = format!("\n{}", stmt);
477        assert_eq!(
478            r#"
479CREATE TABLE IF NOT EXISTS "legacy_json" (
480  "j" JSON NULL,
481  "ts" TIMESTAMP(3) NOT NULL,
482  TIME INDEX ("ts")
483)
484ENGINE=mito
485"#,
486            sql
487        );
488    }
489
490    #[test]
491    fn test_show_create_external_table_sql() {
492        let schema = vec![
493            ColumnSchema::new("host", ConcreteDataType::string_datatype(), true),
494            ColumnSchema::new("cpu", ConcreteDataType::float64_datatype(), true),
495        ];
496        let table_schema = SchemaRef::new(Schema::new(schema));
497        let table_name = "system_metrics";
498        let schema_name = "public".to_string();
499        let catalog_name = "greptime".to_string();
500        let mut options: TableOptions = Default::default();
501        let _ = options
502            .extra_options
503            .insert(FILE_TABLE_LOCATION_KEY.to_string(), "foo.csv".to_string());
504        let _ = options.extra_options.insert(
505            FILE_TABLE_META_KEY.to_string(),
506            "{{\"files\":[\"foo.csv\"]}}".to_string(),
507        );
508        let _ = options
509            .extra_options
510            .insert(FILE_TABLE_FORMAT_KEY.to_string(), "csv".to_string());
511        let meta = TableMetaBuilder::empty()
512            .schema(table_schema)
513            .primary_key_indices(vec![])
514            .engine("file".to_string())
515            .next_column_id(0)
516            .options(options)
517            .created_on(Default::default())
518            .build()
519            .unwrap();
520
521        let info = Arc::new(
522            TableInfoBuilder::default()
523                .table_id(1024)
524                .table_version(0 as TableVersion)
525                .name(table_name)
526                .schema_name(schema_name)
527                .catalog_name(catalog_name)
528                .desc(None)
529                .table_type(TableType::Base)
530                .meta(meta)
531                .build()
532                .unwrap(),
533        );
534
535        let stmt = create_table_stmt(&info, None, '"').unwrap();
536
537        let sql = format!("\n{}", stmt);
538        assert_eq!(
539            r#"
540CREATE EXTERNAL TABLE IF NOT EXISTS "system_metrics" (
541  "host" STRING NULL,
542  "cpu" DOUBLE NULL,
543
544)
545ENGINE=file
546WITH(
547  format = 'csv',
548  location = 'foo.csv'
549)"#,
550            sql
551        );
552    }
553
554    #[test]
555    fn test_show_create_table_redacts_relational_meta() {
556        let table_schema = SchemaRef::new(Schema::new(vec![]));
557        let mut options: TableOptions = Default::default();
558        let _ = options.extra_options.insert(
559            RELATIONAL_META_KEY.to_string(),
560            "{\"connection\":\"mysql://user:secret@127.0.0.1:3306/db\",\"remote_table\":\"sample\"}"
561                .to_string(),
562        );
563        let _ = options
564            .extra_options
565            .insert(FILE_TABLE_LOCATION_KEY.to_string(), "foo.csv".to_string());
566        let meta = TableMetaBuilder::empty()
567            .schema(table_schema)
568            .primary_key_indices(vec![])
569            .engine("relational".to_string())
570            .next_column_id(0)
571            .options(options)
572            .created_on(Default::default())
573            .build()
574            .unwrap();
575
576        let opts = create_sql_options(&meta, None);
577        let str_map = opts.to_str_map();
578        assert!(!str_map.contains_key(RELATIONAL_META_KEY));
579        assert!(str_map.contains_key(FILE_TABLE_LOCATION_KEY));
580        assert!(str_map.contains_key(RELATIONAL_CONNECTION_KEY));
581        assert_eq!(str_map.get(RELATIONAL_REMOTE_TABLE_KEY), Some(&"sample"));
582    }
583
584    #[test]
585    fn test_show_create_external_relational_table_sql() {
586        let table_schema = SchemaRef::new(Schema::new(vec![]));
587        let table_name = "system_metrics";
588        let mut options: TableOptions = Default::default();
589        let _ = options.extra_options.insert(
590            RELATIONAL_META_KEY.to_string(),
591            "{\"connection\":\"mysql://user:secret@127.0.0.1:3306/db\",\"remote_table\":\"sample\"}"
592                .to_string(),
593        );
594        let meta = TableMetaBuilder::empty()
595            .schema(table_schema)
596            .primary_key_indices(vec![])
597            .engine("relational".to_string())
598            .next_column_id(0)
599            .options(options)
600            .created_on(Default::default())
601            .build()
602            .unwrap();
603
604        let info = Arc::new(
605            TableInfoBuilder::default()
606                .table_id(1024)
607                .table_version(0 as TableVersion)
608                .name(table_name)
609                .schema_name("public".to_string())
610                .catalog_name("greptime".to_string())
611                .desc(None)
612                .table_type(TableType::Base)
613                .meta(meta)
614                .build()
615                .unwrap(),
616        );
617
618        let stmt = create_table_stmt(&info, None, '"').unwrap();
619        let sql = format!("{stmt}");
620        assert!(sql.contains("CREATE EXTERNAL TABLE"));
621        assert!(sql.contains("ENGINE=relational"));
622        assert!(sql.contains("connection = '******'"));
623        assert!(sql.contains("remote_table = 'sample'"));
624        assert!(!sql.contains("secret"));
625    }
626}