ALTER STAGE

Description

ALTER STAGE is used to modify the attributes of an existing named internal or external stage.

Note

Cluster administrators (i.e., root users) and account administrators can modify the data stage.

Syntax

1. > ALTER STAGE [ IF EXISTS ] { stage_name }
2. { StageParams }
3. [ directoryTableParams ]
4. [ COMMENT = '<string_literal>' ]

6. StageParams (for Amazon S3) :
7. URL =  "endpoint"='<string>' CREDENTIALS = {"access_key_id"='<string>', "secret_access_key"='<string>'}

9. StageParams (for Aliyun OSS) :
10. URL =  "endpoint"='<string>' CREDENTIALS = {"access_key_id"='<string>', "secret_access_key"='<string>'}

12. StageParams (for File System) :
13. URL= 'filepath'

15. directoryTableParams :
16. ENABLE = { TRUE | FALSE }

Explanations

  • IF NOT EXISTS: An optional parameter used to check whether a stage with the same name already exists when modifying a stage, avoiding duplicate creations.
  • stage_name: The name of the stage to be modified.

  • StageParams: This parameter group is used to specify the stage’s configuration parameters.

  • endpoint: The connection URL for the stage, indicating the location of the object storage service. This URL’s content may vary for object storage services like Amazon S3, Aliyun OSS, or a file system. For example s3.us-west-2.amazonaws.com

  • CREDENTIALS: This JSON object contains the credentials required to connect to the object storage service, such as access_key_id, secret_access_key, etc.

  • directoryTableParams: This parameter group is used to specify the configuration of a directory table associated with the stage.

  • ENABLE: Indicates whether the directory table is enabled, with values TRUE or FALSE values.

Examples

1. CREATE TABLE `user` (`id` int(11) ,`user_name` varchar(255) ,`sex` varchar(255));
2. INSERT INTO user(id,user_name,sex) values('1', 'weder', 'man'), ('2', 'tom', 'man'), ('3', 'wederTom', 'man');

4. -- Create internal data stage
5. mysql> CREATE STAGE stage1 URL='/tmp' ENABLE = TRUE;

7. -- Export data from the table to data stage
8. mysql> SELECT * FROM user INTO OUTFILE 'stage1:/user.csv';
9. -- You can see your exported table in your local directory

11. mysql> SHOW STAGES;
12. +------------+-----------------------------+---------+---------+
13. | STAGE_NAME | URL                         | STATUS  | COMMENT |
14. +------------+-----------------------------+---------+---------+
15. | stage1     | /Users/Prinz/03testrepo/csv | ENABLED |         |
16. +------------+-----------------------------+---------+---------+
17. 1 row in set (0.01 sec)

19. -- modify the stage1
20. mysql> ALTER STAGE stage1 SET COMMENT 'user stage';

22. mysql> SHOW STAGES;
23. +------------+-----------------------------+---------+------------+
24. | STAGE_NAME | URL                         | STATUS  | COMMENT    |
25. +------------+-----------------------------+---------+------------+
26. | stage1     | /Users/Prinz/03testrepo/csv | ENABLED | user stage |
27. +------------+-----------------------------+---------+------------+
28. 1 row in set (0.00 sec)

30. -- disable the data stage named 'stage1'
31. mysql> ALTER STAGE stage1 SET ENABLE = FALSE;
32. Query OK, 0 rows affected (0.00 sec)

34. -- Try to export the data of the user table to the data stage named 'stage1:/user.csv', but stage1 has been disabled, so it is no longer available, and an error is reported
35. mysql> SELECT * FROM user INTO OUTFILE 'stage1:/user.csv';
36. ERROR 20101 (HY000): internal error: stage 'stage1' is invalid, please check

38. -- Re-enable with a data stage named 'stage1'
39. mysql> ALTER STAGE stage1 SET ENABLE = TRUE;
40. Query OK, 0 rows affected (0.00 sec)

42. -- The export can be executed successfully again
43. mysql> SELECT * FROM user INTO OUTFILE 'stage1:/user.csv';