突然接到一个任务,需要用java使用Extract API 2.0实现抽取数据库的数并生成.hyper文件。没办法,只能一边看文档一边写个demo先试试看能不能行得通。
文档:https://onlinehelp.tableau.com/current/api/extract_api/en-us/Extract/extract_api.htm
Extract API 2.0安装包下载:https://onlinehelp.tableau.com/current/api/extract_api/en-us/Extract/extract_api_installing.htm#downloading
需要注意的是java使用Extract API 2.0需要把下载的Extract API 2.0包里的dll文件放到jdk的bin文件夹里,并把相关jar包导入到项目当中去。
由于是测试demo,所需我随便用了一个mysql的数据库,看是否能把数据抽取到.hyper文件里去。
下面是代码:
package com.app.dataController;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import com.mysql.jdbc.Connection;
import com.tableausoftware.TableauException;
import com.tableausoftware.common.Collation;
import com.tableausoftware.common.Type;
import com.tableausoftware.hyperextract.Extract;
import com.tableausoftware.hyperextract.ExtractAPI;
import com.tableausoftware.hyperextract.Row;
import com.tableausoftware.hyperextract.Table;
import com.tableausoftware.hyperextract.TableDefinition;
public class ConnectSource {
public static ResultSet consource() {
try {
Class.forName("com.mysql.jdbc.Driver"); //加载MYSQL JDBC驱动程序
System.out.println("Success loading Mysql Driver!");
}
catch (Exception e) {
System.out.print("Error loading Mysql Driver!");
e.printStackTrace();
}
try {
java.sql.Connection connect = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/talend","root","123456");
//连接URL为 jdbc:mysql//服务器地址/数据库名 ,后面的2个参数分别是登陆用户名和密码
System.out.println("Success connect Mysql server!");
Statement stmt = connect.createStatement();
ResultSet rs = stmt.executeQuery("select * from user");
return rs;
}
catch (Exception e) {
System.out.print("get data error!");
e.printStackTrace();
}
return null;
}
public static void main(String args[]) {
HashMap<String, String> options = new HashMap<>();
options.put("filename", "C:\\Users\\Silence\\Desktop\\Silence.hyper");
options.put("build", "1");
if ( options.containsKey( "build" ) ) {
try {
// Initialize the Tableau Extract API
ExtractAPI.initialize();
Extract extract = createOrOpenExtract( options.get( "filename" ), true ,true);
populateExtract( extract, options.containsKey( "spatial" ) );
// Flush the Extract to Disk
extract.close();
// Close the Tableau Extract API
ExtractAPI.cleanup();
}catch ( TableauException e ) {
System.err.println( "A fatal error occurred while opening or closing the Extract API:" );
System.err.println( e.getMessage() );
System.err.println( "Printing stack trace now:" );
e.printStackTrace( System.err );
System.err.println( "Exiting now." );
System.exit( -1 );
}
catch ( Throwable t ) {
System.err.println( "An unknown error occured while opening or closing the Extract API:" );
System.err.println( "Printing stack trace now:" );
t.printStackTrace( System.err );
System.err.println( "Exiting now." );
System.exit( -1 );
}
}
}
private static Extract createOrOpenExtract(String filename,boolean useSpatial,boolean createMultipleTables) {
Extract user = null;
Table table = null;
try {
// Create Extract Object
// (NOTE: TabExtractCreate() opens an existing extract with the given
// filename if one exists or creates a new extract with the given filename
// if one does not)
user = new Extract( filename );
// Define Table Schema (If we are creating a new extract)
// (NOTE: in Tableau Data Engine, all tables must be named "Extract")
if ( !user.hasTable( "Extract" ) ) {
TableDefinition schema = new TableDefinition();
schema.setDefaultCollation( Collation.EN_GB );
schema.addColumn( "ID",Type.INTEGER );
schema.addColumn( "Name",Type.CHAR_STRING );
schema.addColumn( "CountryCode",Type.CHAR_STRING );
schema.addColumn( "District",Type.CHAR_STRING );
schema.addColumn( "Population",Type.CHAR_STRING );
table = user.addTable( "Extract", schema );
if ( table == null ) {
System.err.println( "A fatal error occured while creating the table" );
System.err.println( "Exiting now." );
System.exit( -1 );
}
}
}
catch ( TableauException e ) {
System.err.println( "A fatal error occurred while creating the extract:" );
System.err.println( e.getMessage() );
System.err.println( "Printing stack trace now:" );
e.printStackTrace( System.err );
System.err.println( "Exiting now." );
System.exit( -1 );
}
catch ( Throwable t ) {
System.err.println( "An unknown error occured while creating the extract" );
System.err.println( "Printing stack trace now:" );
t.printStackTrace( System.err );
System.err.println( "Exiting now." );
System.exit( -1 );
}
return user;
}
private static void populateExtract(Extract user,boolean useSpatial) {
try {
// Get Schema
Table table = user.openTable( "Extract" );
TableDefinition tableDef = table.getTableDefinition();
ResultSet rs = consource();
// Insert Data
Row row = new Row( tableDef );
while (rs.next()) {
row.setInteger(0, rs.getInt("ID")); // ID
row.setCharString( 1, rs.getString("Name")); // Name
row.setCharString( 2, rs.getString("CountryCode")); // CountryCode
row.setCharString( 3, rs.getString("District")); // District
row.setCharString( 4, rs.getString("Population")); // Population
table.insert( row );
}
}
catch ( TableauException e ) {
System.err.println( "A fatal error occurred while populating the extract:" );
System.err.println( e.getMessage() );
System.err.println( "Printing stack trace now:" );
e.printStackTrace( System.err );
System.err.println( "Exiting now." );
System.exit( -1 );
}
catch ( Throwable t ) {
System.err.println( "An unknown error occured while populating the extract" );
System.err.println( "Printing stack trace now:" );
t.printStackTrace( System.err );
System.err.println( "Exiting now." );
System.exit( -1 );
}
}
}
环境配置好之后可以直接执行main方法即可生成.hyper文件。
生成的文件用Tableau打开效果:
接下来就该开始用java连接RedShift库,把亿级的数据通过多线程的方式生成.hyper文件啦。