Redshift 通過(guò)重新建表修改varchar(60) 到varchar(90)

首先需要知道的是redshift不支持SQL語(yǔ)法直接修改varchar(60) 到varchar(90)，但PostgreSQL和MySQL都支持。

1.PostgreSQL如何修改varchar(60)到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,'aaa'),(2,'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) |

2.MySQL如何修改varchar(60)到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(1,'aaa'),(2,'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    |       |

+-------+-------------+------+-----+---------+-------+

3.redshift如何修改varchar(60)到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(15,6),

    current_lon numeric(15,6),

    altitude numeric(15,6),

    horizontal_accuracy numeric(15,6),

    vertical_accuracy numeric(15,6),

    nav_timestamp timestamp without time zone,

    speed numeric(15,6),

    assumed_speed numeric(15,6),

    traffic_speed numeric(15,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(15,6),

    origin_lon numeric(15,6),

    dest_lat numeric(15,6),

    dest_lon numeric(15,6),

    impression_id character varying(200),

    autosuggest_iid character varying(200),

    "position" smallint,

    distance numeric(20,4),

    duration numeric(20,4),

    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(20,4),

    centroid_lat numeric(15,6),

    centroid_lon numeric(15,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(5,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(5,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(15,6),

    min_lat numeric(15,6),

    max_lon numeric(15,6),

    min_lon numeric(15,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(15,6),

    recommended_assumed_speed numeric(15,6),

    recommended_traffic_speed numeric(15,6),

    recommended_edge_id character varying(20),

    recommended_distance numeric(20,4),

    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 06:10:37.725145+00

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:47:48.722736+00

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:有兩個(gè)對(duì)表字段較多情況下的處理方法：

awk '{print $1}' temp.txt|sed 's#$#,#g'

awk -F "|" '{print $1,$2}' temp.txt|sed 's#$#,#g'