GBase 8a 迁移 GreenPlum 常用sql(3)

查询表大小

gbase:表大小

select table_data_size/1024/1024/1024,table_storage_size/1024/1024/1024 from information_schema.cluster_tables where table_schema='dbname' and table_name='t1';

gp:表大小

select pg_size_pretty(pg_total_relation_size('db1.tb1')) as size;

被标记已删除的数据大小:

select DELETABLE_SIZE/1024/1024/1024 from tables where table_schema='db1' and table_name='tb1'\G;

转储:

create table bak.t1as select * from db1.tb1;

数据加载

load data infile 'ftp://username:passwd@host//FTP/gbase/ftp' into table db1.tb1data_format 3 HAVING LINES SEPARATOR fields terminated by '\x1f'  fields enclosed by '"'  null_value 'null' lines terminated by '\r\n' datetime format '%Y-%m-%d %H:%i:%s.%f' max_bad_records 0  fields PRESERVE BLANKS  fields table_fields col1,col2' 

执行load语句的文件及目录权限:

        1.文件不需要gbase为属主(不需要 chown gbase:gbase 文件),只需要有可读权限:即xx4   如704
        2.路径不需要gbase为属主(不需要 chown gbase:gbase 目录),只需要有可执行权限: 即xx1 如 701
        3.如使用ftp加载,且不想使用/home/gbase目录作为加载目录，需要在vsftp.conf文件，将chroot_loacl_user = YES注释，或者改为NO

关于校验字符集collate

        SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
        SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;