外表支持读取 orc、parquet 格式文件的使用说明

[root@observer ~]# obclient -h127.0.0.1 -uroot@mysql#kunpeng_clu -P2883 -pxxx -A -c test
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 341
Server version: OceanBase 4.3.5.0 (r100010012025011314-c5d637e8a6116ad5d62d23f3a337459fcc144c9f) (Built Jan 13 2025 14:48:17)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient(root@mysql)[test]> create table test (
name varchar(2000),
sex char(10),
birth_date datetime
);
Query OK, 0 rows affected (0.189 sec)

obclient(root@mysql)[test]> delimiter $$
obclient(root@mysql)[test]> create procedure insert_test(num int)
begin
  declare fname char(1);
  declare name1 char(1);
  declare name2 char(1);
  declare v_name varchar(2000);
  declare v_sex int;
  declare v_birth date;
  declare i int default 1;
  while i <= num do
    set fname = substring('赵钱孙李周吴郑王孙',floor(1+8*rand()),1);  -- 姓
    set name1 = substring('一二三四五六七八九十甲乙丙丁',floor(1+14*rand()),1); -- 抽一个字
    set v_name = rpad(fname,350,name1);
    set v_sex = round(rand());
    set v_birth = date_add('2000-01-01', interval round(rand() * 31*12*22) day);
    insert into test(name,sex,birth_date) values(v_name,v_sex,v_birth);  -- 插入数据
    set i = i + 1;
    end while;
end
$$
obclient(root@mysql)[test]> delimiter ;
Query OK, 0 rows affected (0.143 sec)

obclient(root@mysql)[test]> call insert_test(10000);
Query OK, 1 row affected (8.884 sec)

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 10000 rows affected (0.170 sec)
Records: 10000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 20000 rows affected (0.344 sec)
Records: 20000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 40000 rows affected (0.686 sec)
Records: 40000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 80000 rows affected (1.495 sec)
Records: 80000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> insert into test select * from test;
Query OK, 160000 rows affected (2.735 sec)
Records: 160000  Duplicates: 0  Warnings: 0

obclient(root@mysql)[test]> commit;
Query OK, 0 rows affected (0.001 sec)

obclient(root@mysql)[test]> select count(*) from test;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.345 sec)

obclient(root@mysql)[test]> SELECT * FROM test
INTO outfile '/home/admin/test_dir/test'
FORMAT(
  TYPE = 'ORC'
) single=false;
Query OK, 320000 rows affected (0.912 sec)

obclient(root@mysql)[test]> Bye

[root@observer ~]# ls -ltr /home/admin/test_dir/
total 332612
-rw-r--r-- 1 admin admin 324237806 Feb  5 11:32 test_0.orc
-rw-r--r-- 1 admin admin  16352573 Feb  5 11:32 test_1.orc
[root@observer ~]#

obclient(root@mysql)[test]> CREATE EXTERNAL TABLE test_external (
    name varchar(2000),
    sex char(10),
    birth_date datetime
)
LOCATION = '/home/admin/test_dir/'
FORMAT = (
  TYPE = 'ORC'
)
PATTERN = 'test_[0-9]*.orc';
Query OK, 0 rows affected (0.138 sec)

obclient(root@mysql)[test]> select count(*) from test_external;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.015 sec)

obclient(root@mysql)[test]> SELECT * FROM test
INTO outfile '/home/admin/test_dir/test'
FORMAT(
  TYPE = 'PARQUET'
) single=false;
Query OK, 320000 rows affected (0.984 sec)

obclient(root@mysql)[test]> Bye

[root@observer ~]# ls -ltr /home/admin/test_dir/
total 992
-rw-r--r-- 1 admin admin 1013517 Feb  5 11:42 test_0.parquet
[root@observer ~]#

obclient(root@mysql)[test]> CREATE EXTERNAL TABLE test_external (
    name varchar(2000),
    sex char(10),
    birth_date datetime
)
LOCATION = '/home/admin/test_dir/'
FORMAT = (
  TYPE = 'PARQUET'
)
PATTERN = 'test_[0-9]*.parquet';
Query OK, 0 rows affected (0.134 sec)

obclient(root@mysql)[test]> select count(*) from test_external;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.011 sec)

obclient(root@mysql)[test]> SELECT * FROM test
INTO outfile '/home/admin/test_dir/test'
FORMAT(
  TYPE = 'ORC'
  COMPRESSION = 'ZSTD'
  COMPRESSION_BLOCK_SIZE = '64KB'
) single=false;
Query OK, 320000 rows affected (0.791 sec)

obclient(root@mysql)[test]> Bye

[root@observer ~]# ls -ltr /home/admin/test_dir/
total 3032
-rw-r--r-- 1 admin admin 3102074 Feb  5 11:57 test_0.orc
[root@observer ~]#

obclient(root@mysql)[test]> CREATE EXTERNAL TABLE test_external (
    name varchar(2000),
    sex char(10),
    birth_date datetime
)
LOCATION = '/home/admin/test_dir/'
FORMAT = (
  TYPE = 'ORC'
)
PATTERN = 'test_[0-9]*.orc';
Query OK, 0 rows affected (0.132 sec)

obclient(root@mysql)[test]> select count(*) from test_external;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.010 sec)

obclient(root@mysql)[test]> SELECT * FROM test
INTO outfile '/home/admin/test_dir/test'
FORMAT(
  TYPE = 'PARQUET'
  COMPRESSION = 'SNAPPY'
  COMPRESSION_BLOCK_SIZE = '64KB'
) single=false;
Query OK, 320000 rows affected (1.003 sec)

obclient(root@mysql)[test]> Bye

[root@observer ~]# ls -ltr /home/admin/test_dir/
total 432
-rw-r--r-- 1 admin admin 439411 Feb  5 11:46 test_0.parquet
[root@observer ~]#

obclient(root@mysql)[test]> CREATE EXTERNAL TABLE test_external (
    name varchar(2000),
    sex char(10),
    birth_date datetime
)
LOCATION = '/home/admin/test_dir/'
FORMAT = (
  TYPE = 'PARQUET'
)
PATTERN = 'test_[0-9]*.parquet';
Query OK, 0 rows affected (0.137 sec)

obclient(root@mysql)[test]> select count(*) from test_external;
+----------+
| count(*) |
+----------+
|   320000 |
+----------+
1 row in set (0.009 sec)