更新时间:2023-07-19 GMT+08:00
表相关
创建DLI表
DLI提供创建DLI表的接口。您可以使用该接口创建数据存储在DLI内部的表。示例代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
private static Table createDLITable(Database database) throws DLIException { //构造表列集合,通过实例化Column对象构建列 List<Column> columns = new ArrayList<Column>(); Column c1 = new Column("c1", DataType.STRING, "desc for c1"); Column c2 = new Column("c2", DataType.INT, "desc for c2"); Column c3 = new Column("c3", DataType.DOUBLE, "desc for c3"); Column c4 = new Column("c4", DataType.BIGINT, "desc for c4"); Column c5 = new Column("c5", DataType.SHORT, "desc for c5"); Column c6 = new Column("c6", DataType.LONG, "desc for c6"); Column c7 = new Column("c7", DataType.SMALLINT, "desc for c7"); Column c8 = new Column("c8", DataType.BOOLEAN, "desc for c8"); Column c9 = new Column("c9", DataType.DATE, "desc for c9"); Column c10 = new Column("c10", DataType.TIMESTAMP, "desc for c10"); Column c11 = new Column("c11", DataType.DECIMAL, "desc for c11"); columns.add(c1); columns.add(c2); columns.add(c3); columns.add(c4); columns.add(c5); columns.add(c6); columns.add(c7); columns.add(c8); columns.add(c9); columns.add(c10); columns.add(c11); List<String> sortColumns = new ArrayList<String>(); sortColumns.add("c1"); String DLITblName = "tablename"; String desc = "desc for table"; //通过调用Database对象的createDLITable方法创建DLI表 Table table = database.createDLITable(DLITblName, desc, columns, sortColumns); System.out.println(table); return table; } |
DataType.DECIMAL的默认精度为(10,0),设置Decimal类型精度的方法如下:
1
|
Column c11 = new Column("c11", new DecimalTypeInfo(25,5), "test for c11"); |
创建OBS表
DLI提供创建OBS表的接口。您可以使用该接口创建数据存储在OBS的表。示例代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
private static Table createObsTable(Database database) throws DLIException { //构造表列集合,通过实例化Column对象构建列 List < Column > columns = new ArrayList < Column > (); Column c1 = new Column("c1", DataType.STRING, "desc for c1"); Column c2 = new Column("c2", DataType.INT, "desc for c2"); Column c3 = new Column("c3", DataType.DOUBLE, "desc for c3"); Column c4 = new Column("c4", DataType.BIGINT, "desc for c4"); Column c5 = new Column("c5", DataType.SHORT, "desc for c5"); Column c6 = new Column("c6", DataType.LONG, "desc for c6"); Column c7 = new Column("c7", DataType.SMALLINT, "desc for c7"); Column c8 = new Column("c8", DataType.BOOLEAN, "desc for c8"); Column c9 = new Column("c9", DataType.DATE, "desc for c9"); Column c10 = new Column("c10", DataType.TIMESTAMP, "desc for c10"); Column c11 = new Column("c11", DataType.DECIMAL, "desc for c11"); columns.add(c1); columns.add(c2); columns.add(c3); columns.add(c4); columns.add(c5); columns.add(c6); columns.add(c7); columns.add(c8); columns.add(c9); columns.add(c10); columns.add(c11); CsvFormatInfo formatInfo = new CsvFormatInfo(); formatInfo.setWithColumnHeader(true); formatInfo.setDelimiter(","); formatInfo.setQuoteChar("\""); formatInfo.setEscapeChar("\\"); formatInfo.setDateFormat("yyyy/MM/dd"); formatInfo.setTimestampFormat("yyyy-MM-dd HH:mm:ss"); String obsTblName = "tablename"; String desc = "desc for table"; String dataPath = "OBS path"; //通过调用Database对象的createObsTable方法创建OBS表 Table table = database.createObsTable(obsTblName, desc, columns,StorageType.CSV , dataPath, formatInfo); System.out.println(table); return table; } |
DataType.DECIMAL的默认精度为(10,0),设置Decimal类型精度的方法如下:
1
|
Column c11 = new Column("c11", new DecimalTypeInfo(25,5), "test for c11"); |
删除表
DLI提供删除表的接口。您可以使用该接口删除数据库下的所有表。示例代码如下:
1 2 3 4 5 6 7 8 9 |
private static void deleteTables(Database database) throws DLIException { //调用Database对象的listAllTables接口查询所有表 List<Table> tables = database.listAllTables(); for (Table table : tables) { //遍历表,调用Table对象的deleteTable接口删除表 table.deleteTable(); System.out.println("delete table " + table.getTableName()); } } |
表删除后,将不可恢复,请谨慎操作。
查询所有表
DLI提供创建查询表的接口。您可以使用该接口查询数据库下的所有表。示例代码如下:
1 2 3 4 5 6 7 |
private static void listTables(Database database) throws DLIException { //调用Database对象的listAllTables方法查询数据库下的所有表 List<Table> tables = database.listAllTables(true); for (Table table : tables) { System.out.println(table); } } |
查询表的分区信息(包含分区的创建和修改时间)
DLI提供查询表分区信息的接口。您可以使用该接口查询数据库下表的分区信息(包括分区的创建和修改时间)。示例代码如下:
1 2 3 4 5 6 7 8 9 10 11 |
private static void showPartitionsInfo(DLIClient client) throws DLIException { String databaseName = "databasename"; String tableName = "tablename"; //调用DLIClient对象的showPartitions方法查询数据库下表的分区信息(包括分区的创建和修改时间) PartitionResult partitionResult = client.showPartitions(databaseName, tableName); PartitionListInfo partitonInfos = partitionResult.getPartitions(); //获取分区的创建和修改时间 Long createTime = partitonInfos.getPartitionInfos().get(0).getCreateTime().longValue(); Long lastAccessTime = partitonInfos.getPartitionInfos().get(0).getLastAccessTime().longValue(); System.out.println("createTime:"+createTime+"\nlastAccessTime:"+lastAccessTime); } |
描述表信息
您可以使用该接口获取表的元数据描述信息。示例代码如下:
private static void getTableDetail(Table table) throws DLIException { // 调用Table对象的getTableDetail方法获取描述表信息 // TableSchema tableSchema=table.getTableDetail(); //输出信息 System.out.println(List<Column> columns = tableSchema.getColumns()); System.out.println(List<String> sortColumns = tableSchema.getSortColumns()); System.out.println(String createTableSql = tableSchema.getCreateTableSql()); System.out.println(String tableComment = tableSchema.getTableComment()); }
父主题: SQL作业相关