sqlserver 元数据管理

sqlserver 数据字段

SELECT CASE
         WHEN c.colorder = 1 THEN o.NAME
         ELSE ''
       END      AS 表名,
       c.NAME   列名,
       t.NAME   取值类型,
       g.[value] AS 创建时间
FROM   sysobjects o,
       dbo.syscolumns c
LEFT OUTER JOIN dbo.systypes t
  ON c.xtype = t.xusertype
LEFT OUTER JOIN sys.extended_properties as g
  on c.id = g.major_id
 and c.colid = g.minor_id

WHERE  o.xtype = 'u'
  AND c.id = o.id  
  and o.name = 'table_name'

hive 批量导出 DDL

#!/bin/bash -x

#输入数据库
DB=$1

tables=$(hive --showHeader=false -e "use $DB; show tables;")
for table in $(echo $tables | sed 's/[|+-]/ /g');
do
   echo "--============ db: $DB , table: $table ============" >> ./hive_table_create.hql
   echo "$(hive -e "use $DB;show create table $table;");" | sed 's/[|+-]/ /g' | sed 's/createtab_stmt//g' >> ./hive_table_create.hql
done

建表语句的转换

sqlserver -> hive

SELECT  table_name 
        ,'drop table if exists vn0c43l.' + table_name + ';' + char(10)+  
         'create table vn0c43l.' + table_name + '('         + char(10)+
                       LEFT(column_names , LEN(column_names )-2)              + char(10)+
                    ') partitioned by (ts string)'                                                       + char(10)+
                    'stored as orc ;'      AS column_names 

                ,'sqoop import '    + 
                  '--driver com.microsoft.sqlserver.jdbc.SQLServerDriver \' + char(10)+ 
                  '--connect "jdbc:sqlserver://ip;username=; password=;database=" \' + char(10) +
                  '--table '+ table_name +' \'+ char(10)+
                  '--hive-table dwd.'+ table_catalog + '__' + table_name +' \' + char(10)+
                  '--map-column-hive row_version=string \' + char(10) +
                  '-m 1 --hive-overwrite --hive-import --delete-target-dir; '  + char(10)+
                     ' if [ "$?" -eq "0" ]; then '                              + char(10)+
                          '      echo " '+ table_name +' ok" >> status.txt '    + char(10)+
                     ' fi '                        as sqoop_script
       FROM information_schema.columns AS extern
CROSS APPLY
(
    SELECT '`' + c.NAME + '`  ' + case when charindex('char',t.NAME) > 0 
                              then 'string'
    when charindex('int',t.NAME) > 0 
    then 'int'
                              else 'double' end                 
              + ' comment '''+cast(g.[value] as varchar(2000))+''','+ char(10) 
		FROM   sysobjects o,
		       dbo.syscolumns c
		LEFT OUTER JOIN dbo.systypes t
		  ON c.xtype = t.xusertype
		LEFT OUTER JOIN sys.extended_properties as g
		  on c.id = g.major_id
		 and c.colid = g.minor_id
		WHERE  o.xtype = 'u'
		  AND c.id = o.id  
		  and o.name = extern.table_name
     FOR XML PATH('')
) pre_trimmed (column_names)
   where extern.table_catalog = 'walmart_manager_recap' 
     and extern.table_name like 'rpt%'
GROUP BY table_catalog 
       , table_name
       , column_names;

字段列表

   select c.NAME + ',' + char(10)
		FROM   sysobjects o,
		       dbo.syscolumns c
		LEFT OUTER JOIN dbo.systypes t
		  ON c.xtype = t.xusertype
		LEFT OUTER JOIN sys.extended_properties as g
		  on c.id = g.major_id
		 and c.colid = g.minor_id
		WHERE  o.xtype = 'u'
		  AND c.id = o.id  
		  and o.name = '表名称'
     FOR XML PATH('')

where 语句的拼接

declare @whereClause varchar(2000)

-- 建一个临时表
select clause into #whereClause
  from (
     select cast('' as varchar(2000)) as clause
  ) as a where 1<>1
 -- 将 where 中的条件插入导临时表中
 if len(@var) > 0
 begin 
   insert into #whereClause select ' field {expression} ' + @var 
 end   
 -- 将临时表中的数据做类似 ' and '.join(array) 的操作
if exists(select 1 from #whereClause)
begin

set @whereClause=' where ' + stuff((select ' and ' + clause from #whereClause for xml path('')),1,4,'')

end