pgsql到hive字段映射及建表

最新推荐文章于 2024-04-24 06:15:00 发布

进击的怒汉

最新推荐文章于 2024-04-24 06:15:00 发布

阅读量1.6k

点赞数

分类专栏： sql 文章标签： dws hive

本文链接：https://blog.csdn.net/jiguanglong/article/details/103826555

版权

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

select
    case when t.ordinal_position = 1 
             then 'CREATE TABLE IF NOT EXISTS sjzcpt_jc.' ||t.table_schema|| '_' || t.table_name || chr(10)||'('
	     else ','
	end
	||case when t.column_name='sjlybz' then 'sjlybz_1' else t.column_name end||' '
	||case
          when t.data_type in ('bpchar') and t.character_maximum_length<256 then
           'CHAR(' || t.character_maximum_length || ')'
          when t.data_type in ('varchar') and t.character_maximum_length<65536 then
           'VARCHAR(' || t.character_maximum_length|| ')'
          when t.data_type in ('timestamp','timestamptz') then
           'TIMESTAMP'
          when t.data_type in ('int1','int2') then
           'SMALLINT'
          when t.data_type ='int4'  then
           'INT'
          when t.data_type = 'int8'  then
           'BIGINT'
          when t.data_type in ('float4','float8') then
           'DOUBLE'
          when t.data_type = 'numeric' then
           'DECIMAL(' || t.numeric_precision||','|| t.numeric_scale || ')'
          else
           'STRING'
      end
	||' COMMENT '||chr(39)|| case when t.is_primary_key is not null then 'primary_key:' end
	||t.column_description||chr(39)
	|| case
          when max(t.ordinal_position) over(partition by t.oid) = t.ordinal_position 
		  then chr(10)||',YPTETL_SJ TIMESTAMP COMMENT  ' ||CHR(39)||'云平台ETL时间'||CHR(39)||')'
		  ||'COMMENT '''||t.table_description||''' PARTITIONED BY'||chr(10)
		  ||'(rfq CHAR(8) COMMENT ' ||CHR(39)|| '日分区'||CHR(39)||chr(10)
          ||',sjlybz VARCHAR(20)  COMMENT ' ||CHR(39)||'数据来源标志'||CHR(39)||');'
		end as sql
from (select 
(nc.nspname)::information_schema.sql_identifier AS table_schema, 
(c.relname)::information_schema.sql_identifier AS table_name, 
(a.attname)::information_schema.sql_identifier AS column_name, 
(a.attnum)::information_schema.cardinal_number AS ordinal_position,
(t.typname)::information_schema.character_data AS data_type,
(information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number AS character_maximum_length,
(information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number AS numeric_precision, 
(information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number AS numeric_scale,
(d.description) as column_description,
(c.oid) as oid,
(de.description) as table_description,
(con.conkey) as is_primary_key
FROM (    
          (select * from pg_attribute where attnum > 0 AND (NOT attisdropped))a 
	      JOIN 
	      (select oid,relowner,relname,relnamespace from pg_class where relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) c ON a.attrelid = c.oid
	      JOIN 
	      (select nspname,oid from pg_namespace  where (NOT pg_is_other_temp_schema(oid)))nc  ON c.relnamespace = nc.oid
		  JOIN 
		  pg_type t ON a.atttypid = t.oid
		  left join
		  pg_description d on d.objoid = c.oid and d.objsubid=a.attnum	
		  left join
          (select objoid,description from pg_description where objsubid=0) de on de.objoid = c.oid
          left join 
          (select conrelid,conkey from pg_constraint where contype='p') con on con.conrelid = c.oid and a.attnum = any(con.conkey)
	 )  
WHERE 
    (pg_has_role(c.relowner, 'USAGE'::text) 
    OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text))
	) t
order by  t.oid,t.ordinal_position;