Greenplum常⽤命令、函数
Greenplum常⽤查询命令
#查看test_bd事务(即数据库)下的所有表名包含 user 的表信息
SELECT UPPER(A.SCHEMANAME) AS SCHEMANAME, UPPER(A.TABLENAME) AS TABLENAME,
D.ATTRELID,D.ATTRELID :: regclass,UPPER(D.ATTNAME) AS ATTNAME,
REPLACE(REPLACE(REPLACE(FORMAT_TYPE(D.ATTTYPID, D.ATTTYPMOD),'numeric','NUMBER'),
'character varying','VARCHAR2'),'date','DATE') AS DATA_TYPE,E.DESCRIPTION
FROM PG_TABLES AS A
INNER JOIN PG_CLASS AS B ON A.TABLENAME = B.RELNAME
LEFT JOIN PG_CATALOG.PG_DESCRIPTION AS E ON B.OID = E.OBJOID
LEFT JOIN PG_CATALOG.PG_ATTRIBUTE AS D ON D.ATTRELID = E.OBJOID AND D.ATTNUM = E.OBJSUBID
WHERE SCHEMANAME ='test_bd'
AND A.TABLENAME LIKE'%user%'
AND D.ATTNUM >0
ORDER BY A .TABLENAME,D.ATTNUM
  select pg_size_pretty(pg_database_size('gp_db'));    　#查看gp数据库⼤⼩
  select gp_segment_id,count(*) from db_name.tb_name group by gp_segment_id;    　#查看数据分布情况
1.创建数据库  createdb test_db;
2.删除数据库  dropdb test_db;
3.创建模式  　create schema myschema;
4.删除模式  　drop schema myschema;
5.创建⽤户  　create user user_name with password '123456' ;
6.删除⽤户  　drop user user_name;
7.查看系统⽤户信息  select usename from pg_user;
8.查看版本信息    select version();
9.打开psql交互⼯具  psql name_db;
10.执⾏sql⽂件    mydb=> \i basics.sql \i 命令从指定的⽂件中读取命令。
11.批量将⽂本⽂件中内容导⼊到wether表      copy weather from '/home/';
12.查看搜索模式    show search_path;
13.设置搜索模式    set search_path to myschema,public;
14.创建表空间    create tablespace spacename_tb location 'file_path';
tabletotal函数15.显⽰默认表空间  show default_tablespace;
16.设置默认表空间  set default_tablespace=表空间名称;
17.指定⽤户登录    psql mtps -u
18.显⽰当前系统时间  select now() ;
19.配置plpgsql语⾔  create language 'plpgsql' handler plpgsql_call_handler;
20.删除规则  drop rule name on relation [ cascade | restrict ];
21.当前⽇期属于⼀年中第⼏周  select extract(week from timestamp '2020-06-14');
22.查询表是否存在  select * from pg_statio_user_tables where relname='test_tb';
23.导出表      ./pg_dump -p 端⼝号 -u ⽤户 -t 表名称 -f 备份⽂件位置数据库 ;
24.整个数据库导出  pg_dumpall -d -p 端⼝号 -h 服务器ip -u postgres(⽤户名) > /home/xiaop/all.bak
25.数据库备份恢复  psql -h 192.168.0.48 -p 5433 -u postgres
26.数据库备份    pg_dumpall -h 192.168.0.4 -p 5433 -u postgres >/databack/postgresql2020061401.dmp
27.当前⽇期函数    select current_date;
28.返回第⼗条开始的5条记录  select * from tbname limit 5 offset 10;
29.查看数据库⼤⼩  select pg_size_pretty(pg_database_size('mtps')) as fulldbsize;
30.查看数据库表⼤⼩  select pg_size_pretty(pg_total_relation_size('test_db.t_l_collectfile')) as
fulltblsize,pg_size_pretty(pg_relation_size('test_db.t_l_collectfile')) as justthetblsize;
31.设置执⾏超过指定秒数的sql语句输出到⽇志  log_min_duration_statement = 3
32.超过⼀定秒数sql⾃动执⾏执⾏计划  　shared_preload_libraries = 'auto_explain',custom_variable_classes =
'auto_explain',auto_explain.log_min_duration = 4s
33.数据库备份
  select pg_start_backup('backup baseline');
  select pg_stop_backup();
  f
  restore_command='cp /opt/buxlog/%f %p'
34.数据字典查看表结构  select column_name, data_type from lumns where table_name = 'test_tb';
35.查询表结构      select a.attnum,a.attname as pname as type,a.attlen as length,a.atttypmod as lengthvar,a.attnotnull
as notnull from pg_class c,pg_attribute a,pg_type t lname=表名称and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid
36.将查询结果直接输出到⽂件，在psql中 \o ⽂件路径
  select datname,rolname from pg_database a left outer join pg_roles b on a.datdba=b.oid; \o
37.查询数据库所有则  select datname,rolname from pg_database a left outer join pg_roles b on a.datdba=b.oid ;
38.结束正在执⾏的事务  select * from pg_stat_activity;
39.查看被锁定表
    select lname as table, pg_database.datname as database, pid, mode, granted from pg_locks, pg_class, pg_database where lation = pg_class.oid and pg_locks.database = pg_database.oid;
40.查看客户端连接情况    select client_addr ,client_port,waiting,query_start,current_query from pg_stat_activity;
41.常看数据库.conf配置    show all;
  修改数据库f参数
  修改f内容 pg_ctl reload
  回滚⽇志强制恢复 pg_resetxlog -f 数据库⽂件路径
Greenplum常⽤命令与MySQL对⽐
  (1)列出所有的数据库
    mysql: show databases
    psql: \l或\list
  (2)切换数据库
    mysql: use dbname
    psql: \c dbname
  (3)列出当前数据库下的数据表
    mysql: show tables
    psql: \d
  (4)列出指定表的所有字段
    mysql: show columns from table name
    psql: \d tablename
  (5)查看指定表的基本情况
    mysql: describe tablename
    psql: \d+ tablename
  (6)退出登录
    mysql: quit 或者\q
    psql:\q
Greenplum常⽤调优命令
netstat -ano|grep 5432 -wc              　#查看连接5432端⼝线程数
psql -d gpdatabase -h master_host -p 5432 -U gpadmin  #连接数据库
psql gpdb_name                  　#连接gpdb_name数据库
vacuum table_name                  #清除数据库碎⽚
  #查看索引的⼤⼩
  select pg_size_pretty(pg_relation_size('ind_t_id'));
  pg_restore -d test_db test_db.dump          　#导⼊dump数据
  pg_dump -U gpadmin -Fc test_db > test.dump      　#导⼊dump数据
  gp数据导⼊：
  psql -h localhost -p 5432 -d test_db -U gpadmin -c "\\copy tablename FROM '/home/user/test.csv' with DELIMITER as ',' NULL as 'null string'"
  psql -d test_db -h localhost -p dd -c "\copy(select * from ods.ods_dPmytest)to /home/gpadmin/loadout/test_db.csv"
  gp数据备份：
  gp_dump database_name;
  COPY TO  把⼀个表的所有内容都拷贝到⼀个⽂件，COPY只能⽤于表，不能⽤于视图。
  COPY FROM  从⼀个⽂件⾥拷贝数据到⼀个表⾥(把数据附加到表中已经存在的内容⾥)。
  COPY user_tb TO '/tmp/data/test.csv' WITH csv; 导出表数据
  COPY user_tb FROM '/tmp/data/test.csv' WITH csv; 导⼊表数据
  # 使⽤select命令
  COPY (select * from user_tb create_time > '2019-08-16 00:00:00' and create_time < '2019-08-17 00:00:00') TO
'/tmp/data/user_20190816.csv' with csv;
Greenplum常⽤窗⼝函数
  row_number() over(partition by XXX order by XXX desc);    -- ⽣成序列号
  max() over(partition by XXX order by XXX);          --取最⼤值  avg() over(partition by XXX order by XXX);          --取平均值

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。