云服务器内容精选

  • 参数说明 表1 冲突的锁模式 请求的锁模式/当前锁模式 ACCESS SHARE ROW SHARE ROW EXCLUSIVE SHARE UPDATE EXCLUSIVE SHARE SHARE ROW EXCLUSIVE EXCLUSIVE ACCESS EXCLUSIVE UPDATE EXCLUSIVE ACCESS SHARE - - - - - - - X - ROW SHARE - - - - - - X X - ROW EXCLUSIVE - - - - X X X X - SHARE UPDATE EXCLUSIVE - - - X X X X X - SHARE - - X X - X X X X SHARE ROW EXCLUSIVE - - X X X X X X X EXCLUSIVE - X X X X X X X X ACCESS EXCLUSIVE X X X X X X X X X UPDATE EXCLUSIVE - - - - X X X X X LOCK的参数说明如下所示: name 要锁定的表的名字,可以有模式修饰。 LOCK TABLE命令中声明的表的顺序就是上锁的顺序。 取值范围:已存在的表名。 ONLY 如果指定ONLY只有该表被锁定,如果没有声明该表和他的所有子表将都被锁定。 ACCESS SHARE ACCESS锁只允许对表进行读取,而禁止对表进行修改。所有对表进行读取而不修改的SQL语句都会自动请求这种锁。例如,SELECT命令会自动在被引用的表上请求一个这种锁。 ROW SHARE ROW SHARE锁允许对表进行并发读取,禁止对表进行其他操作。 SELECT FOR UPDATE和SELECT FOR SHARE命令会自动在目标表上请求ROW SHARE锁(且所有被引用但不是FOR SHARE/FOR UPDATE的其他表上,还会自动加上ACCESS SHARE锁)。 ROW EXCLUSIVE 与ROW SHARE锁不同,ROW EXCLUSIVE允许并发读取表,也允许修改表中的数据。UPDATE,DELETE,INSERT命令会自动在目标表上请求这个锁(且所有被引用的其他表上还会自动加上的ACCESS SHARE锁)。通常情况下,所有会修改表数据的命令都会请求表的ROW EXCLUSIVE锁。 SHARE UPDATE EXCLUSIVE 这个模式保护一个表的模式不被并发修改,以及禁止在目标表上执行垃圾回收命令(VACUUM )。 VACUUM(不带FULL选项),ANALYZE,CREATE INDEX CONCURRENTLY命令会自动请求这样的锁。 SHARE SHARE锁允许并发的查询,但是禁止对表进行修改。 CREATE INDEX(不带CONCURRENTLY选项)语句会自动请求这种锁。 SHARE ROW EXCLUSIVE SHARE ROW EXCLUSIVE锁禁止对表进行任何的并发修改,而且是独占锁,因此一个会话中只能获取一次。 任何SQL语句都不会自动请求这个锁模式。 EXCLUSIVE EXCLUSIVE锁允许对目标表进行并发查询,但是禁止任何其他操作。 这个模式只允许并发加ACCESS SHARE锁,也就是说,只有对表的读动作可以和持有这个锁模式的事务并发执行。 任何SQL语句都不会在用户表上自动请求这个锁模式。然而在某些操作的时候,会在某些系统表上请求它。 ACCESS EXCLUSIVE 这个模式保证其所有者(事务)是可以访问该表的唯一事务。 ALTER TABLE,DROP TABLE,TRUNCATE,REINDEX,CLUSTER,VACUUM FULL命令会自动请求这种锁。 在LOCK TABLE命令没有明确声明需要的锁模式时,它是缺省锁模式。 UPDATE EXCLUSIVE UPDATE EXCLUSIVE 锁允许并发(AUTO)VACUUM和(AUTO)ANALYZE,但不允许多个(AUTO)VACUUM并发。 该参数仅8.2.1.300及以上集群版本支持。 UPDATE EXCLUSIVE锁模式仅在VACUUM语法中使用。 NOWAIT 声明LOCK TABLE不去等待任何冲突的锁释放,如果无法立即获取该锁,该命令退出并且发出一个错误信息。 在不指定NOWAIT的情况下获取表级锁时,如果有其他互斥锁存在的话,则等待其他锁的释放。 LOCAL COORDINATOR ONLY 声明LOCK TABLE只在接收当前会话请求的CN上执行,而不会下发给其他CN和所有DN。该选项仅针对元数据的操作,以提高效率。 该参数仅8.2.0.100以上集群版本支持。 当前仅支持ACCESS SHARE锁模式,其他锁模式会报错。
  • 注意事项 LOCK TABLE只能在一个事务块的内部有用,因为锁在事务结束时就会被释放。出现在任意事务块外面的LOCK TABLE都会报错。 如果没有声明锁模式,缺省为最严格的模式ACCESS EXCLUSIVE。 LOCK TABLE ... IN ACCESS SHARE MODE需要在目标表上有SELECT权限。所有其他形式的LOCK需要UPDATE和/或DELETE权限。 没有UNLOCK TABLE命令,锁总是在事务结束时释放。 LOCK TABLE只处理表级的锁,因此那些带“ROW”字样的锁模式都是有歧义的。这些模式名字通常可理解为用户试图在一个被锁定的表中获取行级的锁。同样,ROW EXCLUSIVE模式也是一个可共享的表级锁。注意,只要是涉及到LOCK TABLE ,所有锁模式都有相同的语意,区别仅在于规则中锁与锁之间是否冲突,规则请参见表1。
  • 语法格式 1 2 3 LOCK [ TABLE ] {[ ONLY ] name [, ...]| {name [ * ]} [, ...]} [ IN {ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE | UPDATE EXCLUSIVE} MODE ] [ NOWAIT ] [LOCAL COORDINATOR ONLY];
  • 功能描述 LOCK TABLE获取表级锁。 当自动获取引用表的命令的锁时,GaussDB(DWS)会始终使用限制最小的锁模式。如果用户需要一种更为严格的锁模式,可以使用LOCK命令。例如,某个应用是在Read Committed隔离级别上运行事务,并且需要保证表中的数据在事务运行期间保持稳定。为实现这个目的,则可以在查询之前对表使用SHARE锁模式进行锁定。这样将防止并发数据更改,并确保后续的查询可以读到已提交的持久化的数据。因为SHARE锁模式与任何写操作需要的ROW EXCLUSIVE模式冲突,并且LOCK TABLE name IN SHARE MODE语句将等到所有当前持有ROW EXCLUSIVE模式锁的事务提交或回滚后才能执行。因此,一旦获得该锁,就不会存在未提交的写操作,此外其他操作也只能等到该锁释放之后才能开始。
  • 示例 示例1: delete from h0 where column1 = 'country'; 示例2: delete from h0 where column1 IN ('country1', 'country2'); 示例3: delete from h0 where column1 IN (select column11 from sourceTable2); 示例4: delete from h0 where column1 IN (select column11 from sourceTable2 where column1 = 'xxx'); 示例5: delete from h0;
  • 描述 向表中插入新的数据行。 如果指定了列名列表,那么这些列名列表必须与query语句产生列列表名完全匹配。表中不在列名列表中的每一列,其值会设置为null。 如果没有指定列名列表,则query语句产生的列必须与将要插入的列完全匹配。 使用insert into时,会往表中追加数据,而使用insert overwrite时,如果表属性“auto.purge”被设置为“true”,直接删除原表数据,再写入新的数据。 如果对象表是分区表时,insert overwrite会删除对应分区的数据而非所有数据。 insert into后面的table关键字为可选,以兼容hive语法。
  • 语法 INSERT { INTO | OVERWRITE } [TABLE] table_name [(column_list)] [ PARTITION (partition_clause)] {select_statement | VALUES (value [, value ...]) [, (value [, value ...]) ...] } FROM from_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement FROM from_statement INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) select_statement
  • 示例 创建fruit和fruit_copy表: create table fruit (name varchar,price double); create table fruit_copy (name varchar,price double); 向fruit表中插入一行数据: insert into fruit values('LIchee',32); -- 兼容写法示例,带上table关键字 insert into table fruit values('Cherry',88); 向fruit表中插入多行数据: insert into fruit values('banana',10),('peach',6),('lemon',12),('apple',7); 将fruit表中的数据行加载到fruit_copy表中,执行后表中有5条记录: insert into fruit_copy select * from fruit; 先清空fruit_copy表,再将fruit中的数据加载到表中,执行之后表中有2条记录: insert overwrite fruit_copy select * from fruit limit 2; 对于varchar类型,仅当目标表定义的列字段长度大于源表的实际字段长度时,才可以使用INSERT... SELECT...的形式从源表中查数据并且插入到目标表: create table varchar50(c1 varchar(50)); insert into varchar50 values('hetuEngine'); create table varchar100(c1 varchar(100)); insert into varchar100 select * from varchar50; 分区表使用insert overwrite语句时,只会清理插入值所在分区的数据,而不是整个表: --创建表 create table test_part (id int, alias varchar) partitioned by (dept_id int, status varchar); insert into test_part partition(dept_id=10, status='good') values (1, 'xyz'), (2, 'abc'); select * from test_part order by id; id | alias | dept_id | status ----|-------|---------|-------- 1 | xyz | 10 | good 2 | abc | 10 | good (2 rows) --清理分区partition(dept_id=25, status='overwrite'),并插入一条数据 insert overwrite test_part (id, alias, dept_id, status) values (3, 'uvw', 25, 'overwrite'); select * from test_part ; id | alias | dept_id | status ----|-------|---------|----------- 1 | xyz | 10 | good 2 | abc | 10 | good 3 | uvw | 25 | overwrite --清理分区partition(dept_id=10, status='good'),并插入一条数据 insert overwrite test_part (id, alias, dept_id, status) values (4, 'new', 10, 'good'); select * from test_part ordr; id | alias | dept_id | status ----|-------|---------|----------- 3 | uvw | 25 | overwrite 4 | new | 10 | good (2 rows) --分区表插入数据 create table test_p_1(name string, age int) partitioned by (provice string, city string); create table test_p_2(name string, age int) partitioned by (provice string, city string); -- 填充数据到test_p_1 insert into test_p_1 partition (provice = 'hebei', city= 'baoding') values ('xiaobei',15),( 'xiaoming',22); -- 根据test_p_1 插入数据到test_p_2 -- 方式一 from test_p_1 insert into table test_p_2 partition (provice = 'hebei', city= 'baoding') select name,age; -- 方式二 insert into test_p_2 partition(provice = 'hebei', city= 'baoding') select name,age from test_p_1;
  • 参数说明 plan_hint子句 以/*+ */的形式在关键字后,用于对指定语句块生成的计划进行hint调优,详细用法请参见使用Plan Hint进行调优。 table_name 要更新的表名,可以使用模式修饰。 取值范围:已存在的表名称。 alias 目标表的别名。 取值范围:字符串,符合标识符命名规范。 column_name 要修改的字段名。 支持使用目标表的表名加字段名来引用这个字段。例如: 1 UPDATE foo SET foo.col_name = 'GaussDB'; 支持使用目标表的别名加字段名来引用这个字段。例如: 1 UPDATE foo AS f SET f.col_name = 'GaussDB'; 取值范围:已存在的字段名。 expression 赋给字段的值或表达式。 DEFAULT 用对应字段的缺省值填充该字段。 如果没有缺省值,则为NULL。 sub_query 子查询。 使用同一数据库里其他表的信息来更新一个表可以使用子查询的方法。其中SELECT子句具体介绍请参考SELECT。 from_list 一个表的表达式列表,允许在WHERE条件里使用其他表的字段。与在一个SELECT语句的FROM子句里声明表列表类似。 目标表绝对不能出现在from_list里,除非在使用一个自连接(此时它必须以from_list的别名出现)。 condition 一个返回boolean类型结果的表达式。只有这个表达式返回true的行才会被更新。 output_expression 在所有需要更新的行都被更新之后,UPDATE命令用于计算返回值的表达式。 取值范围:使用任何table以及FROM中列出的表的字段。*表示返回所有字段。 output_name 字段的返回名称。
  • 注意事项 要修改表,用户必须对该表有UPDATE权限。 对expression或condition条件里涉及到的任何表要有SELECT权限。 不允许对表的分布列(distribute column)进行修改。 对于列存表,暂时不支持RETURNING子句。 列存表不支持结果不确定的更新(non-deterministic update)。试图对列存表用多行数据更新一行时会报错。 列存表的更新操作,旧记录空间不会回收,需要执行VACUUM FULL table_name进行清理。 UPDATE操作频繁的表不建议创建为复制表。 对于列存表,支持轻量化UPDATE操作。轻量化UPDATE只重写更新列,减少空间使用量。列存轻量化UPDATE通过GUC参数enable_light_colupdate控制是否开启,默认关闭。 列存轻量化UPDATE在以下场景不能使用:更新索引列,更新主键列,更新分区列,更新PCK列和在线扩容,会自动转化为普通UPDATE方式。 列存轻量化UPDATE与后台列存AUTOVACUUM并发会小概率报错,可以通过ALTER TABLE设置表级参数enable_column_autovacuum_garbage为off来避免。需要注意的是设置表级参数enable_column_autovacuum_garbage为off会关闭该表的后台列存AUTOVACUUM。
  • 语法格式 1 2 3 4 5 6 7 8 9 10 11 12 13 14 UPDATE [/*+ plan_hint */] [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET {column_name = { expression | DEFAULT } |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...] [ FROM from_list] [ WHERE condition ] [ RETURNING {* | {output_expression [ [ AS ] output_name ]} [, ...] }]; where sub_query can be: SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] { * | {expression [ [ AS ] output_name ]} [, ...] } [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ]
  • 语法格式 从一个文件拷贝数据到一个表: 1 2 3 4 5 6 7 8 9 10 COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | STDIN } [ [ USING ] DELIMITERS 'delimiters' ] [ WITHOUT ESCAPING ] [ LOG ERRORS ] [ LOG ERRORS data ] [ REJECT LIMIT 'limit' ] [ [ WITH ] ( option [, ...] ) ] | copy_option | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [ ...] ] ]; 语法中的FIXED FORMATTER ( { column_name( offset, length ) } [, ...] )以及 [ ( option [, ...] ) | copy_option [ ...] ] 可以任意排列组合。 把一个表的数据拷贝到一个文件: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 COPY table_name [ ( column_name [, ...] ) ] TO { 'filename' | STDOUT } [ [ USING ] DELIMITERS 'delimiters' ] [ WITHOUT ESCAPING ] [ [ WITH ] ( option [, ...] ) ] | copy_option | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [ ...] ] ]; COPY query TO { 'filename' | STDOUT } [ WITHOUT ESCAPING ] [ [ WITH ] ( option [, ...] ) ] | copy_option | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [ ...] ] ]; COPY TO语法形式约束如下: (query)与[USING] DELIMITER不兼容,即若COPY TO的数据来自于一个query的查询结果,那么COPY TO语法不能再指定[USING] DELIMITERS语法子句。 对于FIXED FORMATTTER语法后面跟随的copy_option是以空格进行分隔的。 copy_option是指COPY原生的参数形式,而option是兼容外表导入的参数形式。 语法中的FIXED FORMATTER ( { column_name( offset, length ) } [, ...] )以及 [ ( option [, ...] ) | copy_option [ ...] ] 可以任意排列组合。 其中可选参数option子句语法为: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 FORMAT 'format_name' | OIDS [ boolean ] | DELIMITER 'delimiter_character' | NULL 'null_string' | HEADER [ boolean ] | FILEHEADER 'header_file_string' | FREEZE [ boolean ] | QUOTE 'quote_character' | ESCAPE 'escape_character' | EOL 'newline_character' | NOESCAPING [ boolean ] | FORCE_QUOTE { ( column_name [, ...] ) | * } | FORCE_NOT_NULL ( column_name [, ...] ) | ENCODING 'encoding_name' | IGNORE_EXTRA_DATA [ boolean ] | FILL_MISSING_FIELDS [ boolean ] | COMPATIBLE_ILLEGAL_CHARS [ boolean ] | PRESERVE_BLANKS [ boolean ] | DATE_FORMAT 'date_format_string' | TIME_FORMAT 'time_format_string' | TIMESTAMP_FORMAT 'timestamp_format_string' | SMALLDATETIME_FORMAT 'smalldatetime_format_string' | SERVER 'obs_server_string' | BOM [ boolean ] | MAXROW [ integer ] | FILEPREFIX 'file_prefix_string' 其中可选参数copy_option子句语法为: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 OIDS | NULL 'null_string' | HEADER | FILEHEADER 'header_file_string' | FREEZE | FORCE_NOT_NULL column_name [, ...] | FORCE_QUOTE { column_name [, ...] | * } | BINARY | CSV | QUOTE [ AS ] 'quote_character' | ESCAPE [ AS ] 'escape_character' | EOL 'newline_character' | ENCODING 'encoding_name' | IGNORE_EXTRA_DATA | FILL_MISSING_FIELDS | COMPATIBLE_ILLEGAL_CHARS | PRESERVE_BLANKS | DATE_FORMAT 'date_format_string' | TIME_FORMAT 'time_format_string' | TIMESTAMP_FORMAT 'timestamp_format_string' | SMALLDATETIME_FORMAT 'smalldatetime_format_string'
  • 示例 将tpcds.ship_mode中的数据拷贝到/home/omm/ds_ship_mode.dat文件中: 1 COPY tpcds.ship_mode TO '/home/omm/ds_ship_mode.dat'; 将tpcds.ship_mode 输出到stdout: 1 COPY tpcds.ship_mode TO stdout; 创建tpcds.ship_mode_t1表: 1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE tpcds.ship_mode_t1 ( SM_SHIP_MODE_SK INTEGER NOT NULL, SM_SHIP_MODE_ID CHAR(16) NOT NULL, SM_TYPE CHAR(30) , SM_CODE CHAR(10) , SM_CARRIER CHAR(20) , SM_CONTRACT CHAR(20) ) WITH (ORIENTATION = COLUMN,COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(SM_SHIP_MODE_SK ); 从stdin拷贝数据到表tpcds.ship_mode_t1: 1 COPY tpcds.ship_mode_t1 FROM stdin; 从/home/omm/ds_ship_mode.dat文件拷贝数据到表tpcds.ship_mode_t1: 1 COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat'; 从/home/omm/ds_ship_mode.dat文件拷贝数据到表tpcds.ship_mode_t1,使用参数如下:导入格式为TEXT(format 'text'),分隔符为'\t'(delimiter E'\t'),忽略多余列(ignore_extra_data 'true'),不指定转义(noescaping 'true'): 1 COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' WITH(format 'text', delimiter E'\t', ignore_extra_data 'true', noescaping 'true'); 从/home/omm/ds_ship_mode.dat文件拷贝数据到表tpcds.ship_mode_t1,使用参数如下:导入格式为FIXED(FIXED),指定定长格式(FORMATTER(SM_SHIP_MODE_SK(0, 2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20))),忽略多余列(ignore_extra_data),有数据头(header): 1 COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' FIXED FORMATTER(SM_SHIP_MODE_SK(0, 2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20)) header ignore_extra_data; 从/home/omm/ds_ship_mode.dat文件拷贝数据到表tpcds.ship_mode_t1,使用参数如下:导入格式为FIXED(FIXED),指定定长格式(FORMATTER(SM_SHIP_MODE_SK(0, 2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20))),忽略多余列(ignore_extra_data),有数据头(header),保留末尾 \t 字符: COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' (FORMAT 'fixed', FORMATTER (SM_SHIP_MODE_SK(0,2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20)), PRESERVE_BLANKS‘true', HEADER 'true', IGNORE_EXTRA_DATA 'true'); 将tpcds.ship_mode_t1导出为OBS的 '/bucket/path/'路径上的TEXT格式文件ds_ship_mode.dat。需要指定包含OBS访问信息的“server”option参数: 1 COPY tpcds.ship_mode_t1 TO '/bucket/path/ds_ship_mode.dat' WITH (format 'text', encoding 'utf8', server 'obs_server'); 将tpcds.ship_mode_t1导出为OBS的 '/bucket/path/'路径上的的CSV格式文件。需要指定包含OBS访问信息的“server”option参数。其中文件包含标题行,包含BOM头,单文件最大行数1000行(超出1000行生成新的文件),自定义文件名前缀为“justprefix”: 1 COPY (select * from tpcds.ship_mode_t1 where SM_SHIP_MODE_SK=1060) TO '/bucket/path/' WITH (format 'csv', header 'on', encoding 'utf8', server 'obs_server', bom 'on', maxrow '1000', fileprefix 'justprefix'); 删除tpcds.ship_mode_t1: 1 DROP TABLE tpcds.ship_mode_t1;
  • 注意事项 以安全模式(云上安全模式不支持关闭)启动CN、DN的开关,那么当前模式下禁止使用COPY FROM FILENAME或COPY TO FILENAME语法,可采用\copy的方式进行规避,请参考如何使用\copy导入导出中的示例。 COPY只能用于表,不能用于视图。 对任何要插入数据的表必须有插入权限。 如果声明了一个字段列表,COPY将只在文件和表之间拷贝已声明字段的数据。如果表中有任何不在字段列表里的字段,COPY FROM将为那些字段插入缺省值。 如果声明了数据源文件,服务器必须可以访问该文件;如果指定了STDIN,数据将在客户前端和服务器之间流动,输入时,表的列与列之间使用TAB键分隔,在新的一行中以反斜杠和句点(\.)表示输入结束。 如果数据文件的任意行包含比预期多或者少的字段,COPY FROM将抛出一个错误。 数据的结束可以用一个只包含反斜杠和句点(\.)的行表示。如果从文件中读取数据,数据结束的标记是不必要的;如果在客户端应用之间拷贝数据,必须要有结束标记。 COPY FROM中\N为空字符串,如果要输入实际数据值\N ,使用\\N。
  • 示例 -- 创建事务表 create table upd_tb(col1 int,col2 string) with (format='orc',transactional=true); --插入数据 insert into upd_tb values (3,'A'),(4,'B'); --修改col1 = 4的数据 update upd_tb set col1=5 where col1=4; --查询表,col1=4的记录已被修改 select * from upd_tb; -- col1 | col2 ------|------ 5 | B 3 | A