How to see the DDL of a table in Asterdatabase

Aster

How to see the DDL of a table in Asterdatabase

Hi Team,

I was trying to look for the table definitions using the SQL but failed to do so..

 

I am aware about act command :  \d <schema> <tablename> but that will just show the components not the definition. L

I know I can have the definition from SQL-administrator or TD studio as well but don’t want to use GUI as I am performing an automation and need the Sql command which can provide me the table definition.

I have also tried the NC_Tables but it didn’t help me much. I have prepared this If you have something better please do share the same..

 

SELECT 10 ,
CAST('CREATE'||' '||tabletype||' TABLE'||' '||schemaname || '.' || tablename
||'( '
AS VARCHAR (100)) as a
FROM NC_SYSTEM.nc_all_tables t
-- INNER JOIN NC_SYSTEM.nc_all_columns c
-- ON c.relid = t.tableid
INNER JOIN NC_SYSTEM.nc_all_schemas s
ON t.schemaid = s.schemaid
WHERE s.schemaname in ('vbp_stg_stb')
AND t.tablename in ('hist_stg_103260_004_data1')

UNION

SELECT 20,
CAST(' , '||c.colname ||' '||c.coltype as VARCHAR(100)) as a
FROM NC_SYSTEM.nc_all_tables t
INNER JOIN NC_SYSTEM.nc_all_columns c
ON c.relid = t.tableid
INNER JOIN NC_SYSTEM.nc_all_schemas s
ON t.schemaid = s.schemaid
WHERE s.schemaname in ('vbp_stg_stb')
AND t.tablename in ('hist_stg_103260_004_data1')

UNION

SELECT 30,
CAST(' )'||' '||
case
when
Trim(t.tabletype)=Trim('fact ')
then 'DISTRIBUTE BY HASH'||' ( '|| c.colname ||' ) '
ELSE CAST(NULL AS VARCHAR(100))
END
|| ' ;' AS VARCHAR(100)) as a

FROM NC_SYSTEM.nc_all_tables t
INNER JOIN NC_SYSTEM.nc_all_columns c
ON c.relid = t.tableid
INNER JOIN NC_SYSTEM.nc_all_schemas s
ON t.schemaid = s.schemaid
WHERE s.schemaname in ('vbp_stg_stb')
AND t.tablename in ('hist_stg_103260_004_data1')
AND c.ispartitionkey = 'True'
ORDER BY s.schemaname, t.tablename,c.colnum ;

ORDER BY 1

Thanks in Advance.

1 REPLY
Visitor

Re: How to see the DDL of a table in Asterdatabase

Hello shuhla2502, I have same problem like you, when I have to move data to Hadoop, just to create hadoop table directly from aster definition.

I modify your code And is working quite well.

I hope you can modify to your purpose.

SELECT 10 ,
CAST('CREATE TABLE'||' '|| tablename
||'( '
AS VARCHAR (100)) as a
, 0
FROM NC_SYSTEM.nc_all_tables t
-- INNER JOIN NC_SYSTEM.nc_all_columns c
-- ON c.relid = t.tableid
INNER JOIN NC_SYSTEM.nc_all_schemas s
ON t.schemaid = s.schemaid
WHERE s.schemaname in ('public')
AND t.tablename in ('TABLENAME')

UNION

SELECT 20,
case when c.colnum = 1 then ' '
else ','
end ||
CAST(c.colname ||' '||
case when c.typeid = 20 then c.coltype
when c.typeid = 16 then c.coltype
when c.typeid = 1042 AND c.typemod < 4 then 'CHAR(2)'
when c.typeid = 1042 AND c.typemod > 4 then 'CHAR(' || c.typemod -4 || ')'
when c.typeid = 17 then 'BYNARY'
when c.typeid = 1043 AND c.typemod < 4 then 'STRING'
when c.typeid = 1043 AND c.typemod > 4 then 'VARCHAR(' || c.typemod -4 || ')'
when c.typeid = 1082 then 'DATE'
when c.typeid = 701 then 'DOUBLE'
when c.typeid = 23 then 'INT'
when c.typeid = 1700 AND c.typemod < 4 then 'DECIMAL'
when c.typeid = 1700 AND c.typemod > 4 then 'DECIMAL(' || (c.typemod / 65536) || ','
|| c.typemod - (((c.typemod / 65536) * 65536) + 4) || ') '
when c.typeid = 21 then c.coltype
when c.typeid = 700 then 'FLOAT'
when c.typeid = 25 then 'STRING'
when c.typeid = 1083 then 'TIMESTAMP'
when c.typeid = 1084 then 'TIMESTAMP'
when c.typeid = 1114 then 'TIMESTAMP'
when c.typeid = 2950 then 'VARCHAR(40)'
end as VARCHAR(200) ) as a,
c.colnum
FROM NC_SYSTEM.nc_all_tables t
INNER JOIN NC_SYSTEM.nc_all_columns c
ON c.relid = t.tableid
INNER JOIN NC_SYSTEM.nc_all_schemas s
ON t.schemaid = s.schemaid
WHERE s.schemaname in ('public')
AND t.tablename in ('TABLENAME')

UNION

SELECT 30 ,
CAST( ') \n'
||'ROW FORMAT \n'
||'DELIMITED FIELDS TERMINATED BY ''^'' \n'
||'LINES TERMINATED BY ''\\n'' \n'
||'STORED AS TEXTFILE \n'
||'LOCATION ''/user/hdfs/TABLENAME'' '
AS VARCHAR (200)) as a
, 0

order by 1,3;