Shulou(Shulou.com)06/01 Report--

The first thing to know is that redshift does not support SQL syntax to directly modify varchar (60) to varchar (90), but both PostgreSQL and MySQL do.

How does 1.PostgreSQL modify varchar (60) to varchar (90)?

Syntax:

ALTER TABLE [IF EXISTS] [ONLY] name [*]

Action [,...]

Where action is one of:

ALTER [COLUMN] column_name [SET DATA] TYPE data_type [COLLATE collation] [USING expression]

Eg:

Postgres=# create table T1 (C1 int,c2 varchar (60))

Postgres=# insert into T1 values (1), (2) recorder bbb')

Postgres=# alter table T1 alter c2 type varchar (90)

Postgres=#\ d T1

Table "public.t1"

Column | Type | Modifiers

-+

C1 | integer |

C2 | character varying (90) |

How does 2.MySQL modify varchar (60) to varchar (90)?

Syntax:

ALTER [IGNORE] TABLE tbl_name

[alter_specification [, alter_specification]...]

[partition_options]

MODIFY [COLUMN] col_name column_definition

[FIRST | AFTER col_name]

Eg:

Root@localhost [testdb] > create table T1 (C1 int,c2 varchar (60))

Root@localhost [testdb] > insert into T1 values (1memoriaaaaaa'), (2recorder bbb')

Root@localhost [testdb] > alter table T1 modify c2 varchar (90)

Root@localhost [testdb] > desc T1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | C1 | int (11) | YES | | NULL |

| | c2 | varchar (90) | YES | | NULL | |

+-+ +

How does 3.redshift modify varchar (60) to varchar (90)?

Steps:

(1) Acquire table DDL statement:

Pg_dump-h dpp.cmergb1wezbs.us-west-2.redshift.amazonaws.com-U tnadmin-d analytics-p 5439-s-t facts.auto_events > > auto_events.sql

(2) Create tmp table:

CREATE TABLE facts.auto_events_tmp (

Event_name character varying (30)

Date_id integer

Log_id character varying (60)

Reg_vid character varying (50)

Visitor_id character varying (60)

Carrier character varying (64)

App_id character varying (60)

Entity_id character varying (300)

Utc_timestamp timestamp without time zone

Time_zone character varying (60)

Ad_id character varying (60)

Os_version character varying (20)

Connection_type character varying (10)

Device_make character varying (20)

Device_model character varying (50)

Gps_state character varying (10)

Map_source character varying (10)

Current_lat numeric (15par 6)

Current_lon numeric (15par 6)

Altitude numeric (15par 6)

Horizontal_accuracy numeric (15par 6)

Vertical_accuracy numeric (15par 6)

Nav_timestamp timestamp without time zone

Speed numeric (15par 6)

Assumed_speed numeric (15par 6)

Traffic_speed numeric (15par 6)

Heading character varying (50)

Edge_id character varying (20)

Trigger character varying (30)

Type character varying (50)

Display_screen character varying (50)

Vendor character varying (20)

Incident_vendor character varying (20)

Flow_vendor character varying (20)

Origin_lat numeric (15par 6)

Origin_lon numeric (15par 6)

Dest_lat numeric (15par 6)

Dest_lon numeric (15par 6)

Impression_id character varying (200)

Autosuggest_iid character varying (200)

"position" smallint

Distance numeric (20pd4)

Duration numeric (20pd4)

Incident_count integer

Dest_type character varying (20)

Route_id character varying (60)

Search_id character varying (60)

Nav_id character varying (60)

Start_engine_id character varying (60)

Parent_search_id character varying (60)

Parent_route_id character varying (60)

Parent_log_id character varying (60)

Autosuggest_id character varying (60)

Transaction_id character varying (60)

Term character varying (200)

Status character varying (20)

Description character varying (400)

Display_option character varying (5)

Category_id character varying (20)

Category character varying (100)

Subcategory character varying (20)

Is_sponsored boolean

Notes character varying (400)

Old_entity_id character varying (50)

Number_of_items integer

Region character varying (20)

Home_to_work boolean

Home_to_work_alert character varying (20)

Work_to_home boolean

Work_to_home_alert character varying (20)

Traffic_setting boolean

Camera_setting boolean

Satellite_setting boolean

Action character varying (20)

Zoom_level numeric (20pd4)

Centroid_lat numeric (15par 6)

Centroid_lon numeric (15par 6)

Card_size character varying (10)

Language character varying (20)

Units character varying (10)

Map_color character varying (10)

Map_style character varying (5)

Lane_assist boolean

Backlight character varying (15)

Avoid_highway boolean

Avoid_tolls boolean

Avoid_ferries boolean

Avoid_carpool_lanes boolean

Voice_directions boolean

Voice_traffic boolean

Visual_speed_alert boolean

Audio_speed_alert boolean

Speed_trap boolean

Gas_grade character varying (10)

Location_reporting boolean

Speed_limit boolean

Slogtime timestamp without time zone

Mode character varying (30)

Sort_type character varying (30)

Label character varying (50)

Auto_reroute boolean

Avoid_country_borders boolean

Avoid_tunnels boolean

Avoid_unpaved_roads boolean

Predictive_nav_setting boolean

Mercator_coord_x character varying (30)

Mercator_coord_y character varying (30)

Manufacturer_id character varying (50)

Car_id character varying (100)

Current_city character varying (50)

Current_state character varying (50)

Current_country character varying (50)

Logshed_app_id character varying (30)

Source character varying (20)

Response_time bigint

Sdcard_cid character varying (50)

Time_left smallint

Vin_hash character varying (100)

Model_year character varying (50)

Vehicle_manufacturer character varying (2)

Car_model character varying (5)

Card_id character varying (60)

Destination_id character varying (90)

Confidence numeric (5par 2)

App_version character varying (50)

Device_id character varying (50)

Caused_by character varying (30)

Connected_svcs_product_type integer

Connected_svcs_purchase_state integer

Demo_mode_vehicle_state boolean

Wordsuggest_id character varying (60)

Wordsuggest_list character varying (250)

Wordsuggest_selected_word character varying (50)

Wordsuggest_selected_index smallint

Heading_angle smallint

Request_id character varying (100)

Score numeric (5par 2)

Trigger_cause character varying (300)

Base_transaction_tag character varying (100)

Change_set character varying (250)

Count integer

Db_content character varying (250)

Error_id character varying (100)

Guid character varying (100)

Layer character varying (60)

Max_lat numeric (15par 6)

Min_lat numeric (15par 6)

Max_lon numeric (15par 6)

Min_lon numeric (15par 6)

Size integer

Space_count integer

Space_id character varying (100)

Space_string character varying (250)

State character varying (20)

Summary_id character varying (100)

Transaction_tag character varying (100)

Space character varying (250)

Feedback_list character varying (5000)

Feedback character varying (500)

Method character varying (30)

Alert_congestion character varying (20)

Alert_road_safety character varying (20)

Alert_traffic_camera character varying (20)

Recommended_speed numeric (15par 6)

Recommended_assumed_speed numeric (15par 6)

Recommended_traffic_speed numeric (15par 6)

Recommended_edge_id character varying (20)

Recommended_distance numeric (20pd4)

Recommended_mercator_coord_x character varying (30)

Recommended_mercator_coord_y character varying (30)

);

(3) import data from facts.auto_events to facts.auto_events_tmp

Analytics=# select now ()

Now

-

2017-10-12 06VOUR 37.7251451500

Analytics=# select count (*) from facts.auto_events

Count

-

336840241

Insert into facts.auto_events_tmp select

Event_name

Date_id

Log_id

Reg_vid

Visitor_id

Carrier

App_id

Entity_id

Utc_timestamp

Time_zone

Ad_id

Os_version

Connection_type

Device_make

Device_model

Gps_state

Map_source

Current_lat

Current_lon

Altitude

Horizontal_accuracy

Vertical_accuracy

Nav_timestamp

Speed

Assumed_speed

Traffic_speed

Heading

Edge_id

Trigger

Type

Display_screen

Vendor

Incident_vendor

Flow_vendor

Origin_lat

Origin_lon

Dest_lat

Dest_lon

Impression_id

Autosuggest_iid

Position

Distance

Duration

Incident_count

Dest_type

Route_id

Search_id

Nav_id

Start_engine_id

Parent_search_id

Parent_route_id

Parent_log_id

Autosuggest_id

Transaction_id

Term

Status

Description

Display_option

Category_id

Category

Subcategory

Is_sponsored

Notes

Old_entity_id

Number_of_items

Region

Home_to_work

Home_to_work_alert

Work_to_home

Work_to_home_alert

Traffic_setting

Camera_setting

Satellite_setting

Action

Zoom_level

Centroid_lat

Centroid_lon

Card_size

Language

Units

Map_color

Map_style

Lane_assist

Backlight

Avoid_highway

Avoid_tolls

Avoid_ferries

Avoid_carpool_lanes

Voice_directions

Voice_traffic

Visual_speed_alert

Audio_speed_alert

Speed_trap

Gas_grade

Location_reporting

Speed_limit

Slogtime

Mode

Sort_type

Label

Auto_reroute

Avoid_country_borders

Avoid_tunnels

Avoid_unpaved_roads

Predictive_nav_setting

Mercator_coord_x

Mercator_coord_y

Manufacturer_id

Car_id

Current_city

Current_state

Current_country

Logshed_app_id

Source

Response_time

Sdcard_cid

Time_left

Vin_hash

Vehicle_manufacturer

Car_model

Card_id

Destination_id

Confidence

App_version

Device_id

Caused_by

Connected_svcs_product_type

Connected_svcs_purchase_state

Demo_mode_vehicle_state

Wordsuggest_id

Wordsuggest_list

Wordsuggest_selected_word

Wordsuggest_selected_index

Heading_angle

Request_id

Score

Trigger_cause

Base_transaction_tag

Change_set

Count

Db_content

Error_id

Guid

Layer

Max_lat

Min_lat

Min_lon

Max_lon

Size

Space_count

Space_id

Space_string

State

Summary_id

Transaction_tag

Feedback_list

Feedback

Model_year

Space

Method

Alert_congestion

Alert_road_safety

Alert_traffic_camera

Recommended_speed

Recommended_assumed_speed

Recommended_traffic_speed

Recommended_edge_id

Recommended_distance

Recommended_mercator_coord_x

Recommended_mercator_coord_y

From facts.auto_events

.

INSERT 0 336840241

Analytics=# select now ()

Now

-

2017-10-12 06 purse 47 purl 48.722736 shutt00

Analytics=# select count (*) from facts.auto_events

Count

-

336840241

(4) Modify name:

ALTER TABLE facts.auto_events RENAME TO auto_events_bak_20171012

ALTER TABLE facts.auto_events_tmp RENAME TO auto_events

(5) Grant:

ALTER TABLE auto_events OWNER TO tnadmin

REVOKE ALL ON TABLE auto_events FROM PUBLIC

REVOKE ALL ON TABLE auto_events FROM tnadmin

GRANT ALL ON TABLE auto_events TO tnadmin

GRANT ALL ON TABLE auto_events TO GROUP ops

GRANT SELECT ON TABLE auto_events TO GROUP restricted

GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLE auto_events TO client_events_etl_user

GRANT SELECT ON TABLE auto_events TO cristianap

GRANT SELECT ON TABLE auto_events TO PUBLIC

GRANT SELECT ON TABLE auto_events TO yanhuiw

GRANT SELECT ON TABLE auto_events TO yujiew

Analytics=#\ dp facts.auto_events

Access privileges

Schema | name | type | access privileges

-+-

Facts | auto_events | table | tnadmin=arwdRxt/tnadmin +

| | group ops=arwdRxt/tnadmin + |

| | group restricted=r/tnadmin + |

| | client_events_etl_user=arwdx/tnadmin+ |

| | cristianap=r/tnadmin + |

| = r/tnadmin +

| | yanhuiw=r/tnadmin + |

| | yujiew=r/tnadmin |

PS: there are two ways to handle a large number of table fields:

Awk'{print $1} 'temp.txt | sed's records are written in the same language.

"awk-F" | "'{print $1 camera 2} 'temp.txt | sed's thanks thanks a lot.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.