In table HUM_RESTRICTIONS_AIRLINE reduce VARCHAR sizes from the default max

[sfc-gh-space]

While Snowflake has no issues with the default max sizes of VARCHARs, some consuming BI tools and ODBC calls could be negatively impacted by the large sizes. For that reason, we should work to eliminate the default max sizes of varchars in these models. If you agree, please update table DDL for HUM_RESTRICTIONS_AIRLINE.

Easiest way to make the change is to clone the existing table, create or replace the table with the new field sizes, insert the records from the clone to the newly defined table, and then drop the clone. Code below:

CREATE TABLE TEMP_HUM_RESTRICTIONS_AIRLINE CLONE HUM_RESTRICTIONS_AIRLINE;

create or replace TABLE HUM_RESTRICTIONS_AIRLINE (
	COUNTRY VARCHAR(128),
	ISO3166_1 VARCHAR(2),
	LONG FLOAT,
	LAT FLOAT,
	PUBLISHED DATE,
	SOURCES VARCHAR(1024),
	AIRLINE VARCHAR(256),
	RESTRICTION_TEXT VARCHAR(8192),
	LAST_UPDATE_DATE TIMESTAMP_NTZ(9)
) COMMENT='COVID-19 travel restrictions by airline';

insert into HUM_RESTRICTIONS_AIRLINE (COUNTRY, ISO3166_1, LONG, LAT, PUBLISHED, SOURCES, AIRLINE, RESTRICTION_TEXT, LAST_UPDATE_DATE)
select COUNTRY, ISO3166_1, LONG, LAT, PUBLISHED, SOURCES, AIRLINE, RESTRICTION_TEXT, LAST_UPDATE_DATE
from TEMP_HUM_RESTRICTIONS_AIRLINE;

drop table TEMP_HUM_RESTRICTIONS_AIRLINE;

Thanks!

[tfoldi]

I wish this to be that easy. If I recreate the table with create or replace, I have to re-add manually the tables to data exchange share. Is there any way I can do it from SQL? This is why it is pretty painful to change column types: if I need to recreate the table I have to re-share the table again.

[danteo13]

Maybe you can use ALTER TABLE statements to avoid recreating the table. The order of columns will change though, unless we also add ALTER TABLE statements for all the columns that don't require refactoring:

CREATE OR REPLACE TABLE TEMP_HUM_RESTRICTIONS_AIRLINE CLONE HUM_RESTRICTIONS_AIRLINE;

TRUNCATE TABLE HUM_RESTRICTIONS_AIRLINE;

ALTER TABLE HUM_RESTRICTIONS_AIRLINE DROP COUNTRY;
ALTER TABLE HUM_RESTRICTIONS_AIRLINE ADD COUNTRY VARCHAR(128);

ALTER TABLE HUM_RESTRICTIONS_AIRLINE DROP ISO3166_1;
ALTER TABLE HUM_RESTRICTIONS_AIRLINE ADD ISO3166_1 VARCHAR(2);

ALTER TABLE HUM_RESTRICTIONS_AIRLINE DROP SOURCES;
ALTER TABLE HUM_RESTRICTIONS_AIRLINE ADD SOURCES VARCHAR(1024);

ALTER TABLE HUM_RESTRICTIONS_AIRLINE DROP AIRLINE;
ALTER TABLE HUM_RESTRICTIONS_AIRLINE ADD AIRLINE VARCHAR(256);

ALTER TABLE HUM_RESTRICTIONS_AIRLINE DROP RESTRICTION_TEXT;
ALTER TABLE HUM_RESTRICTIONS_AIRLINE ADD RESTRICTION_TEXT VARCHAR(8192);

INSERT INTO HUM_RESTRICTIONS_AIRLINE (
COUNTRY, ISO3166_1, LONG, LAT, PUBLISHED, SOURCES, AIRLINE, RESTRICTION_TEXT, LAST_UPDATE_DATE
)
SELECT *
FROM TEMP_HUM_RESTRICTIONS_AIRLINE;

DROP TABLE TEMP_HUM_RESTRICTIONS_AIRLINE;

[tfoldi]

Yes, I know, but I would keep the order of the columns. Currently, I delete all columns at the end of the table then add the new one, then add back all the trailing columns. pretty painful.

I would do these changes in one round, define the metadata, generate the replace table statements and re-add tables to the share.