CREATE TABLE demo(host string, cpu double, memory double, jsons JSON, ts TIMESTAMP time index);

Affected Rows: 0

CREATE TABLE demo_2(host string, cpu double, memory double, ts TIMESTAMP time index);

Affected Rows: 0

insert into
    demo(host, cpu, memory, jsons, ts)
values
    ('host1', 66.6, 1024, '{"foo":"bar"}', 1655276557000),
    ('host2', 88.8, 333.3, '{"a":null,"foo":"bar"}', 1655276558000);

Affected Rows: 2

insert into
    demo(host, cpu, memory, ts)
values
    ('host3', 111.1, 444.4, 1722077263000);

Affected Rows: 1

insert into
    demo_2(host, cpu, memory, ts)
values
    ('host4', 77.7, 1111, 1655276555000),
    ('host5', 99.9, 444.4, 1655276556000),
    ('host6', 222.2, 555.5, 1722077264000);

Affected Rows: 3

Copy demo TO '${SQLNESS_HOME}/demo/export/parquet_files/demo.parquet';

Affected Rows: 3

Copy demo_2 TO '${SQLNESS_HOME}/demo/export/parquet_files/demo_2.parquet';

Affected Rows: 3

CREATE TABLE with_filename(host string, cpu double, memory double, ts timestamp time index);

Affected Rows: 0

Copy with_filename FROM '${SQLNESS_HOME}/demo/export/parquet_files/demo.parquet' with (start_time='2022-06-15 07:02:37', end_time='2022-06-15 07:02:39');

Affected Rows: 2

select * from with_filename order by ts;

+-------+------+--------+---------------------+
| host  | cpu  | memory | ts                  |
+-------+------+--------+---------------------+
| host1 | 66.6 | 1024.0 | 2022-06-15T07:02:37 |
| host2 | 88.8 | 333.3  | 2022-06-15T07:02:38 |
+-------+------+--------+---------------------+

CREATE TABLE with_path(host string, cpu double, memory double, ts timestamp time index);

Affected Rows: 0

Copy with_path FROM '${SQLNESS_HOME}/demo/export/parquet_files/';

Affected Rows: 6

select * from with_path order by ts;

+-------+-------+--------+---------------------+
| host  | cpu   | memory | ts                  |
+-------+-------+--------+---------------------+
| host4 | 77.7  | 1111.0 | 2022-06-15T07:02:35 |
| host5 | 99.9  | 444.4  | 2022-06-15T07:02:36 |
| host1 | 66.6  | 1024.0 | 2022-06-15T07:02:37 |
| host2 | 88.8  | 333.3  | 2022-06-15T07:02:38 |
| host3 | 111.1 | 444.4  | 2024-07-27T10:47:43 |
| host6 | 222.2 | 555.5  | 2024-07-27T10:47:44 |
+-------+-------+--------+---------------------+

CREATE TABLE with_json(host string, cpu double, memory double, jsons JSON, ts timestamp time index);

Affected Rows: 0

Copy with_json FROM '${SQLNESS_HOME}/demo/export/parquet_files/demo.parquet';

Affected Rows: 3

select host, cpu, memory, json_to_string(jsons), ts from with_json order by ts;

+-------+-------+--------+---------------------------------+---------------------+
| host  | cpu   | memory | json_to_string(with_json.jsons) | ts                  |
+-------+-------+--------+---------------------------------+---------------------+
| host1 | 66.6  | 1024.0 | {"foo":"bar"}                   | 2022-06-15T07:02:37 |
| host2 | 88.8  | 333.3  | {"a":null,"foo":"bar"}          | 2022-06-15T07:02:38 |
| host3 | 111.1 | 444.4  |                                 | 2024-07-27T10:47:43 |
+-------+-------+--------+---------------------------------+---------------------+

-- SQLNESS PROTOCOL MYSQL
select host, cpu, memory, jsons, ts from demo where host != 'host3';

+-------+------+--------+------------------------+---------------------+
| host  | cpu  | memory | jsons                  | ts                  |
+-------+------+--------+------------------------+---------------------+
| host1 | 66.6 | 1024   | {"foo":"bar"}          | 2022-06-15 07:02:37 |
| host2 | 88.8 | 333.3  | {"a":null,"foo":"bar"} | 2022-06-15 07:02:38 |
+-------+------+--------+------------------------+---------------------+

-- SQLNESS PROTOCOL POSTGRES
select host, cpu, memory, jsons, ts from demo where host != 'host3';

+-------+------+--------+------------------------+----------------------------+
| host  | cpu  | memory | jsons                  | ts                         |
+-------+------+--------+------------------------+----------------------------+
| host1 | 66.6 | 1024.0 | {"foo":"bar"}          | 2022-06-15 07:02:37.000000 |
| host2 | 88.8 | 333.3  | {"a":null,"foo":"bar"} | 2022-06-15 07:02:38.000000 |
+-------+------+--------+------------------------+----------------------------+

CREATE TABLE with_pattern(host string, cpu double, memory double, ts timestamp time index);

Affected Rows: 0

Copy with_pattern FROM '${SQLNESS_HOME}/demo/export/parquet_files/' WITH (PATTERN = 'demo.*', start_time='2022-06-15 07:02:39');

Affected Rows: 2

select * from with_pattern order by ts;

+-------+-------+--------+---------------------+
| host  | cpu   | memory | ts                  |
+-------+-------+--------+---------------------+
| host3 | 111.1 | 444.4  | 2024-07-27T10:47:43 |
| host6 | 222.2 | 555.5  | 2024-07-27T10:47:44 |
+-------+-------+--------+---------------------+

CREATE TABLE without_limit_rows(host string, cpu double, memory double, ts timestamp time index);

Affected Rows: 0

Copy without_limit_rows FROM '${SQLNESS_HOME}/demo/export/parquet_files/';

Affected Rows: 6

select count(*) from without_limit_rows;

+----------+
| count(*) |
+----------+
| 6        |
+----------+

CREATE TABLE with_limit_rows_segment(host string, cpu double, memory double, ts timestamp time index);

Affected Rows: 0

Copy with_limit_rows_segment FROM '${SQLNESS_HOME}/demo/export/parquet_files/' LIMIT 3;

Affected Rows: 3

select count(*) from with_limit_rows_segment;

+----------+
| count(*) |
+----------+
| 3        |
+----------+

Copy with_limit_rows_segment FROM '${SQLNESS_HOME}/demo/export/parquet_files/' LIMIT hello;

Error: 2000(InvalidSyntax), Unexpected token while parsing SQL statement, expected: 'the number of maximum rows', found: ;: sql parser error: Expected: literal int, found: hello at Line: 1, Column: 86

CREATE TABLE demo_with_external_column(host string, cpu double, memory double, ts timestamp time index, external_column string default 'default_value');

Affected Rows: 0

Copy demo_with_external_column FROM '${SQLNESS_HOME}/demo/export/parquet_files/demo.parquet';

Affected Rows: 3

select * from demo_with_external_column order by ts;

+-------+-------+--------+---------------------+-----------------+
| host  | cpu   | memory | ts                  | external_column |
+-------+-------+--------+---------------------+-----------------+
| host1 | 66.6  | 1024.0 | 2022-06-15T07:02:37 | default_value   |
| host2 | 88.8  | 333.3  | 2022-06-15T07:02:38 | default_value   |
| host3 | 111.1 | 444.4  | 2024-07-27T10:47:43 | default_value   |
+-------+-------+--------+---------------------+-----------------+

CREATE TABLE demo_with_less_columns(host string, memory double, ts timestamp time index);

Affected Rows: 0

Copy demo_with_less_columns FROM '${SQLNESS_HOME}/demo/export/parquet_files/demo.parquet';

Affected Rows: 3

select * from demo_with_less_columns order by ts;

+-------+--------+---------------------+
| host  | memory | ts                  |
+-------+--------+---------------------+
| host1 | 1024.0 | 2022-06-15T07:02:37 |
| host2 | 333.3  | 2022-06-15T07:02:38 |
| host3 | 444.4  | 2024-07-27T10:47:43 |
+-------+--------+---------------------+

drop table demo;

Affected Rows: 0

drop table demo_2;

Affected Rows: 0

drop table with_filename;

Affected Rows: 0

drop table with_json;

Affected Rows: 0

drop table with_path;

Affected Rows: 0

drop table with_pattern;

Affected Rows: 0

drop table without_limit_rows;

Affected Rows: 0

drop table with_limit_rows_segment;

Affected Rows: 0

drop table demo_with_external_column;

Affected Rows: 0

drop table demo_with_less_columns;

Affected Rows: 0

