VERTICA EXAMPLE =============== overall_db_script.sql: ________________________________________________________ CREATE SCHEMA STAGING; COMMENT ON SCHEMA STAGING IS 'The landing area for the data warehouse'; CREATE SCHEMA REF; COMMENT ON SCHEMA REF IS 'Primary source of reference data'; CREATE SEQUENCE STAGING.Seq_UserAgent CACHE 100; CREATE TABLE STAGING.UserAgent ( UserAgentId INT NOT NULL DEFAULT nextval('STAGING.Seq_UserAgent'), etc ); ALTER TABLE STAGING.UserAgent ADD CONSTRAINT PK_UserAgent PRIMARY KEY(UserAgentID) DISABLED; COMMENT ON TABLE STAGING.UserAgent IS 'Captures the web browser user agent'; …etc ________________________________________________________ To extract CREATE SCHEMA commands and use AWK to format those commands: ________________________________________________________ grep 'CREATE SCHEMA' ./sql/overall_db_script.sql|awk -F' ' -v dir_target=”schema” -f ./awk/one_line_create.awk >./shell/generate_schema.sh ________________________________________________________ • grep selects the lines we want • -F causes awk to split the line on the space character • -v allows us to pass a named parameter, in this case dir_target, with a value to awk • -f allows us to use a file containing our awk script The one_line_create.awk program: ________________________________________________________ function ltrim(s) {sub(/^[ \t\r\n]+/, “”, s);return s } function ltrim(s) {sub(/[ \t\r\n]+$/, “”, s);return s } function trim(s){return rtrim(ltrim(s));} BEGIN{ print “#!bin/bash”} { entire_line = $0 split($3, test_name, “;”) object_name = test_name[1] if(object_name>””){ printf “echo \”%s\” > ./sql/%s.%s.sql\n”, trim(entire_line, dir_target, object_name } } ________________________________________________________ HOW IT WORKS ============ We told AWK to split our lines using a space, so we get parameters as follows: $1 $2 $3 CREATE SCHEMA STAGING; CREATE SCHEMA REF; I prefer to have named variables rather than $1, $2, $3 etc so that my awk script is more readable. The awk split command is used here to chop off the semicolon at the end of the line. The output from the prinf statement will appear as follows: ________________________________________________________ #!/bin/bash echo “CREATE SCHEMA STAGING;”>./sql/schema/STAGING.sql echo “CREATE SCHEMA REF;”>./sql/schema/REF.sql _________________________________________________________ The result is a file per schema in a folder called /schema/ containing the relevant CREATE SCHEMA command. We use a similar technique for other one line commands we take a similar approach. Sequences also use the one_line_create.awk file The COMMENT ON, ALTER TABLE use their own awk program that works in the same manner but the relevant object is in a different position string.