代码实例
SELECT table_schema
,table_name
,(
CASE
WHEN ORDINAL_POSITION = mincol
AND ORDINAL_POSITION < maxcol
THEN CONCAT (
"create table if not exists "
,table_schema
,"."
,table_name
,"(`"
,column_name
,"` string,"
)
WHEN ORDINAL_POSITION = mincol
AND ORDINAL_POSITION = maxcol
THEN CONCAT (
"create table if not exists "
,table_schema
,"."
,table_name
,"(`"
,column_name
,"` string) row format DELIMITED FIELDS terminated by '\001' stored as textfile location 'ZZZZZZZ/JOB_ANALYZE_LOG';"
)
WHEN ORDINAL_POSITION > mincol
AND ORDINAL_POSITION < maxcol
THEN CONCAT (
"`"
,column_name
,"` string,"
)
WHEN ORDINAL_POSITION = maxcol
THEN CONCAT (
"`"
,column_name
,"` string ) row format DELIMITED FIELDS terminated by '\001' stored as textfile location 'ZZZZZZZ/JOB_ANALYZE_LOG';"
)
END
) AS statement
,ORDINAL_POSITION
,maxcol
,mincol
FROM (
SELECT b.*
,a.maxcol
,a.mincol
FROM (
SELECT table_schema
,table_name
,max(ORDINAL_POSITION) maxcol
,min(ORDINAL_POSITION) mincol
FROM information_schema.COLUMNS
GROUP BY table_schema
,table_name
) a
JOIN (
SELECT table_schema
,table_name
,ORDINAL_POSITION
,column_name
,COLUMN_TYPE
FROM information_schema.COLUMNS
ORDER BY table_schema
,table_name
,ORDINAL_POSITION ASC
) b ON a.table_schema = b.table_schema
AND a.table_name = b.table_name
) c
WHERE table_schema = 'test'
