Automate Salesforce Table Creation With Java

When you ingest data from Salesforce into a relational database, you first need to create a table for the object you want to ingest. Writing a create statement manually is cumbersome and you often need to debug it a few times. Salesforce data types are quite different from database ones. Objects sometimes have a large number of fields (like hundreds).

ETL tools often have an option to create table from Salesforce data ingestion and table creation can done when you ingest the data. However, sometimes your ETL tool does not have an option to create a table with a particular database (e.g. Informatica Cloud does not automatically create tables in Postgres).

I created a small Java application to create a table in a relational database. The example has a data type hash map for MySQL and Postgres. By editing the hash map and adding the appropriate JDBC, this should work for other databases.

Salesforce API Reference

In this post, I used REST-based API. Salesforce also supports WSDL-based SOAP API if you want to use SOAP instead. I found this document (Setting Up Your Java Developer Environment) very useful to get started with Salesforce API with Java. Using Salesforce API with Python, I recommend you to use Simple Salesforce module in my previous post.

Application Design

There are 3 classes with the main method included in the SFAPIMain class. The Database class connects to database with JDBC and execute create table statement. The GetMetaData class gets object metadata from Salesforce and converts it to a create table statement. The SFAPIMain class handles Salesforce API calls and get user input and display outcome. Make sure to build path for appropriate JDBC Jar files.

Compile it as a runnable Jar file. Enter the salesforce object and target schema, then it will create a table for you!

The program also generates a metadata csv and create table statement file. The key for this program to work is to have the correct data type mapping in the code. The only difference between MySQL and Postgres for this code to work is the data type for datetime is datetime in MySQL and timestamp in Postgres. Postgres has a data type called money. However, it is best to use decimal with scale of 2 because money type in Postgres becomes String when you query it with other BI tools. This will cause issues when you try to aggregate the field in the BI tool.

Here comes the code! Enjoy!

Code

SFPIAMain.java

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
package SFAPIMain;

import GenerateStmt.GetMetaData;
import Database.Database;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Iterator;

import javax.swing.JOptionPane;

import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.HttpResponse;
import org.apache.http.HttpStatus;
import org.apache.http.util.EntityUtils;
import org.apache.http.client.ClientProtocolException;
import org.json.JSONObject;
import org.json.JSONTokener;
import org.json.JSONException;

public class SFAPIMain {
   
    static final String USERNAME = "<user name>";
    static final String PASSWORD = "<password + secret token>";
    static final String LOGINURL = "<https://test.salesforce.com or https://login.salesforce.com>";
    static final String GRANTSERVICE = "/services/oauth2/token?grant_type=password";
    static final String CLIENTID = "<Client Id>";
    static final String CLIENTSECRET = "<Client Secret>";
   
    static final String metaDataCsvPath = "<csv path such as metadata.csv>";
    static final String createTableFilePath = "<file path such as ddl.txt>";
   
    // Postgres example. Can add any JBDC as long as you compile the program with the driver.
    static final String className = "<org.postgresql.Driver>";
    static final String connectionUrl = "jdbc:postgresql://<url>:5432/<database name>";
    static final String userName = "<user name for database>";
    static final String passWord = "<password for database>";
   
    public static String outputMessage = "Execution Completed\n\n";
   
    public static void main(String[] args) throws SQLException {
               
        // main method will authenticate, create connection and execute methods from other classes (e.g GetMetaData.java)
        CloseableHttpClient httpclient = HttpClientBuilder.create().build();
       
        // Assemble the login request URL
        String loginURL = LOGINURL + GRANTSERVICE + "&client_id=" + CLIENTID + "&client_secret=" + CLIENTSECRET +
                        "&username=" + USERNAME + "&password=" + PASSWORD;
        System.out.println(loginURL);
       
        // Login requests must be POSTs
        HttpPost httpPost = new HttpPost(loginURL);
        HttpResponse response = null;
       
        // Setting Up Your Java Developer Environment Verify the REST Environment (REST-Based APIs)
        try {
            // Execute the login POST request
            response = httpclient.execute(httpPost);
        }
        catch (ClientProtocolException cpException) {
            // Handle protocol exception
            System.out.println(cpException);
            JOptionPane.showMessageDialog(null, "Client Protocol Exception on Salesforce Authentication: \n"
                                                + cpException.toString(), "EXCEPTION", JOptionPane.ERROR_MESSAGE);
        }
        catch (IOException ioException) {
            // Handle system IO exception
            System.out.println(ioException);
            JOptionPane.showMessageDialog(null, "IO Exception on Salesforce Authentication: \n"
                        + ioException.toString(), "EXCEPTION", JOptionPane.ERROR_MESSAGE);
        }
       
        // verify response is HTTP OK
        final int statusCode = response.getStatusLine().getStatusCode();
        if (statusCode != HttpStatus.SC_OK) {
            System.out.println("Error authenticating to Force.com:"+statusCode);
            JOptionPane.showMessageDialog(null, "Error authenticating to Force.com:"+ statusCode + "\n",
                    "EXCEPTION", JOptionPane.ERROR_MESSAGE);
            // Error is in EntityUtils.toString(response.getEntity())
            return;
        }
        String getResult = null;
       
        try {
            getResult = EntityUtils.toString(response.getEntity());
        }
        catch (IOException ioException) {
            JOptionPane.showMessageDialog(null, "IO Exception to get response from SF authentication:\n"+ ioException.toString(),
                    "EXCEPTION", JOptionPane.ERROR_MESSAGE);
        }
       
        JSONObject jsonObject = null;
        String loginAccessToken = null;
        String loginInstanceUrl = null;
       
        try {
            jsonObject = (JSONObject) new JSONTokener(getResult).nextValue();
            loginAccessToken = jsonObject.getString("access_token");
            loginInstanceUrl = jsonObject.getString("instance_url");
        }
        catch (JSONException jsonException) {
            JOptionPane.showMessageDialog(null, "SF Authentication Response JSON Exception:\n"+ jsonException.toString(),
                    "EXCEPTION", JOptionPane.ERROR_MESSAGE);
        }
       
        System.out.println(response.getStatusLine());
        System.out.println("Successful login");
        System.out.println(" instance URL: "+loginInstanceUrl);
        System.out.println(" access token/session ID:"+loginAccessToken);
        httpPost.releaseConnection();
       
        outputMessage = outputMessage + "Successful Salesforce login\n";
        outputMessage = outputMessage + " instance URL: " + loginInstanceUrl + "\n";
        outputMessage = outputMessage + " access token/session ID: " + loginAccessToken + "\n";
        // getMetaData("Account", loginInstanceUrl, loginAccessToken);
       
        // Get target object
        String targetObject = JOptionPane.showInputDialog("Enter Salesforce Object: ");
        String targetSchema = JOptionPane.showInputDialog("Enter Target Schema for table Creation: ");
       
        // Get metadata and parepare for create table statement
        GetMetaData getMetaData = new GetMetaData(loginInstanceUrl, loginAccessToken);
        String json = getMetaData.generateMetaData(targetObject);
        String createTableStmt = getMetaData.metaDataToCSV(json, metaDataCsvPath, createTableFilePath, targetSchema, targetObject);
       
        // Execute Create Table Statement
        Database mysql = new Database(className, connectionUrl, userName, passWord);
        mysql.createTable(createTableStmt, targetSchema, targetObject);
       
        // Preparing output message
        Iterator<String> iterator = GetMetaData.outputMessage.iterator();
        while (iterator.hasNext()) {
            outputMessage = outputMessage + iterator.next() + "\n";
        }
        Iterator<String> iterator2 = Database.outputMessage.iterator();
        while (iterator2.hasNext()) {
            outputMessage = outputMessage + iterator2.next() + "\n";
        }
        outputMessage += "Salesforce Connection has been released.";
       
        JOptionPane.showMessageDialog(null, outputMessage, "Outcome", JOptionPane.INFORMATION_MESSAGE);
        }
}

GetMetaData.java

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
package GenerateStmt;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;

import javax.swing.JOptionPane;

import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.message.BasicHeader;
import org.apache.http.util.EntityUtils;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;


public class GetMetaData {
    private static HashMap<String, String> map = new HashMap<String, String>();
    private String instanceUrl;
    private String accessToken;
    public static ArrayList<String> outputMessage = new ArrayList<String>();
   
    public GetMetaData(String instanceUrl, String accessToken) {
        this.instanceUrl = instanceUrl;
        this.accessToken = accessToken;
        this.map.put("id","varchar");
        this.map.put("boolean","bool");
        this.map.put("reference","varchar");
        this.map.put("string","varchar");
        this.map.put("picklist","varchar");
        this.map.put("double","decimal");
        this.map.put("phone","varchar");
        this.map.put("currency","decimal");
        this.map.put("int","int");
        // this.map.put("datetime","datetime"); // for MySQL
        this.map.put("datetime","timestamp");
        this.map.put("date","timestamp");
        this.map.put("email","varchar");
        this.map.put("multipicklist","varchar");
        this.map.put("percent","decimal");
        this.map.put("decimal","decimal");
        this.map.put("long","bigint");
        this.map.put("address","varchar");
        this.map.put("masterrecord","varchar");
        this.map.put("location","varchar");
        this.map.put("encryptedstring","varchar");
        this.map.put("textarea","varchar");
        this.map.put("url","varchar");
        this.map.put("combobox", "varchar");
    }
   
    public static String getColumnLength(String type, String typeConverted, String length, String precision, String scale) {
        String param = "";
        if (typeConverted == "varchar") {
            param = String.format("(%s)", length);
        }
        else if (type.equals("currency")) {
            param = String.format("(%s,%s)", precision, "2");
        }
        else if (typeConverted == "decimal") {
            param = String.format("(%s,%s)", precision, scale);
        }
        return param;
    }
   
    public static String setPrimaryKey(String name) {
        String param = "";
        if (name.equals("Id")) {
            param = "primary key";
        }
        return param;
    }
   
    public static String convertColType(String type, String length) {
        String typeConverted = "";
        if (map.containsKey(type)) {
            typeConverted = map.get(type);
                        // Used Integer.parseInt(length) >= 1000 for MySQL. For PGRES, made it to 50000
            if (typeConverted == "varchar" && Integer.parseInt(length) >= 10000) {
                typeConverted = "Text";
            }
        }
        else {
            typeConverted = "Unknown";
        }  
        return typeConverted;
    }

    public String metaDataToCSV (String responseString, String filePathMeta, String filePathSQL, String schema, String targetObject) {
       
        String createTable = "";
        // (1) Open File
        try {
            FileWriter outFileMeta = new FileWriter(filePathMeta);
            FileWriter outFileSQL = new FileWriter(filePathSQL);
            PrintWriter outTextMeta = new PrintWriter(outFileMeta);
            PrintWriter outTextSQL = new PrintWriter(outFileSQL);
           
            outTextMeta.println("name,length,label,type,precision,scale");
            outTextSQL.println(String.format("Create Table %s.%s (", schema, targetObject));
            createTable = createTable + String.format("Create Table %s.%s (", schema, targetObject);
            // (2) Create JSON object
            try {
                JSONObject json = new JSONObject(responseString);
                JSONArray jsonArray = json.getJSONArray("fields");
                int fieldLength = jsonArray.length();
                System.out.println("Length of fields: " + fieldLength);
                int realFieldLength = 0;
                for (int i = 0; i < fieldLength; i++) {
                    String name = json.getJSONArray("fields").getJSONObject(i).getString("name");
                    String length = (String) json.getJSONArray("fields").getJSONObject(i).getString("length");
                    String label = json.getJSONArray("fields").getJSONObject(i).getString("label");
                    String type = json.getJSONArray("fields").getJSONObject(i).getString("type").toLowerCase();
                    String precision = (String) json.getJSONArray("fields").getJSONObject(i).getString("precision");
                    String scale = (String) json.getJSONArray("fields").getJSONObject(i).getString("scale");
                   
                    // (3) Write to metadata csv file
                    outTextMeta.println(String.format("%s,%s,%s,%s,%s,%s", name, length, label, type, precision, scale));
                   
                    // (4) Write Create Statement
                    String convertedType = convertColType(type, length);
                    if (convertedType.equals("varchar") && length.equals("0")) {
                        continue;
                    }
                    String columnLength = getColumnLength(type, convertedType, length, precision, scale);
                    String primaryKey = setPrimaryKey(name);
                    String separator = "";
                    if (i <= fieldLength -2) {
                        separator = ",";
                    }
                    // System.out.println(name);
                   
                    outTextSQL.println(String.format("%s %s%s %s%s", name, convertedType, columnLength, primaryKey, separator));
                    createTable = createTable + String.format("%s %s%s %s%s", name, convertedType, columnLength, primaryKey, separator);
                    realFieldLength += 1;
                } // end of for loop
                outTextMeta.close();
                System.out.println("Number of database columns: " + realFieldLength);
                System.out.println("Meta data csv file has been generated.");
                outputMessage.add("Meta data csv file has been generated.");
                outTextSQL.println(");");
                createTable = createTable + ");";
                outTextSQL.close();
                System.out.println("Create statement has been generated.");
                outputMessage.add("Create statement has been generated.");
            } // end of json try
            catch (JSONException e) {
                e.printStackTrace();
                JOptionPane.showMessageDialog(null, "JSON Error in processing object metadata json: \n" + e.toString(),
                        "EXCEPTION", JOptionPane.ERROR_MESSAGE);
            }
        } // end of file try
        catch (IOException e) {
            JOptionPane.showMessageDialog(null, "File IO Error in generating metadata files: \n" + e.toString(),
                    "EXCEPTION", JOptionPane.ERROR_MESSAGE);
        }
        return createTable;
    }

    public String generateMetaData(String targetObject) {
        // (1) create header for get call
            BasicHeader oAuthHeader = new BasicHeader("Authorization", "OAuth " + accessToken);
            BasicHeader printHeader = new BasicHeader("X-PrettyPrint", "1");
            String jsonString = null;
           
            // (2) create url for api call
            String restString = String.format("/services/data/v41.0/sobjects/%s/describe/", targetObject);
            String finalUrl = instanceUrl + restString;
            System.out.println("Final URL for Rest call: " + finalUrl);
            outputMessage.add("Final URL for Rest call: " + finalUrl);
           
            try {
                // (3) Create httpClient and execute get request
                HttpClient httpClient = HttpClientBuilder.create().build();
                HttpGet httpGet = new HttpGet(finalUrl);
                httpGet.addHeader(oAuthHeader);
                httpGet.addHeader(printHeader);
                HttpResponse httpResponse = httpClient.execute(httpGet);
               
                // (4) Check response status
                int statusCode = httpResponse.getStatusLine().getStatusCode();
                if (statusCode == 200) {
                    String responseString = EntityUtils.toString(httpResponse.getEntity());
                    outputMessage.add("SF metadata call was successfully executed.");
                    // (5) Print Json data
                    try {
                        JSONObject jsonObject = new JSONObject(responseString);
                        // System.out.println("JSON result: |n" + jsonObject.toString(1));
                        jsonString = jsonObject.toString(1);
                    }
                    catch (JSONException exception){
                        exception.printStackTrace();
                        JOptionPane.showMessageDialog(null, "JSON Exception in metadata: \n" + exception.toString(),
                                "EXCEPTION", JOptionPane.ERROR_MESSAGE);
                        System.exit(-1);
                       
                    }
                   
                } // end of if statement when statusCode = 200
               
                else {
                    System.out.println("No response. Status Code: " + statusCode);
                    JOptionPane.showMessageDialog(null, "Meta Data SF call had no response. Status Code: " + statusCode
                            + "\nMost likely cause: Object does not exist.",
                            "EXCEPTION", JOptionPane.ERROR_MESSAGE);
                    System.out.println(httpResponse.getEntity().getContent());
                    System.exit(-1);
                }
               
            }
            catch (ClientProtocolException e) {
               
                e.printStackTrace();
                JOptionPane.showMessageDialog(null, "Client Protocol Error in getting metadata: " + e.toString(),
                        "EXCEPTION", JOptionPane.ERROR_MESSAGE);
                System.exit(-1);
            }
            catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                JOptionPane.showMessageDialog(null, "IO Error in getting metadata: " + e.toString(),
                        "EXCEPTION", JOptionPane.ERROR_MESSAGE);
                System.exit(-1);
            }
            return jsonString;
    }
}

Database.java

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
package Database;

import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Database {
   
    private String className;
    private String connectionUrl;
    private String userName;
    private String passWord;
    public static ArrayList<String> outputMessage = new ArrayList<String>();
   
    public Database (String className, String connectionUrl, String userName, String passWord) {
        this.className = className;
        this.connectionUrl = connectionUrl;
        this.userName = userName;
        this.passWord = passWord;
    }
   
    public void createTable(String sql, String schema, String table) throws SQLException {
        // (1) Load JDBC Driver
        try {
            System.out.println("Loading JDBC Driver...");
            Class.forName(className);
            System.out.println("JDBC loaded successfully.");
            outputMessage.add("JDBC loaded successfully.");
        }
        catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, "JBDC Class Not Found:\n"+ e.toString(),
                    "EXCEPTION", JOptionPane.ERROR_MESSAGE);
            throw new RuntimeException(e);
           
        }
        // (2) Connect to DB and Execute Statement
        Connection connection = null;
        Statement stmt = null;
        String dropTable = String.format("Drop Table If Exists %s.%s;", schema, table);
        try {
            System.out.println("Connecting to Database ...");
            connection = DriverManager.getConnection(connectionUrl, userName, passWord);
            outputMessage.add("Connected to Database.");
            stmt = connection.createStatement();
           
            // Execute Drop Table
            System.out.println(dropTable);
            int rs = stmt.executeUpdate(dropTable);
            System.out.println("Drop Table Statement has been executed. Result code: " + rs);
            outputMessage.add("Drop Table Statement has been executed. Result code: " + rs);
            // Execute Create Table
            System.out.println(sql);
            int rs2 = stmt.executeUpdate(sql);
            System.out.println("Create Table Statement has been executed. Result code: " + rs2);
            outputMessage.add("Create Table Statement has been executed. Result code: " + rs2);
            stmt.executeUpdate("Commit;");
        }
        catch (SQLException e) {
            System.out.println(e.toString());
            JOptionPane.showMessageDialog(null, "SQL Exception:\n"+ e.toString(),
                    "EXCEPTION", JOptionPane.ERROR_MESSAGE);
            System.exit(-1);
        }
        connection.close();
        outputMessage.add("DB connection Closed");
    }
}
Data Engineering
Sending XML Payload and Converting XML Response to JSON with Python

If you need to interact with a REST endpoint that takes a XML string as a payload and returns another XML string as a response, this is the quick guide if you want to use Python. If you want to do it with Node.js, you can check out the post …

Data Engineering
Sending XML Payload and Converting XML Response to JSON with Node.js

Here is the quick Node.js example of interacting with a rest API endpoint that takes XML string as a payload and return with XML string as response. Once we get the response, we will convert it to a JSON object. For this example, we will use the old-school QAS (Quick …

Data Engineering
Downloading All Public GitHub Gist Files

I used to use plug-ins to render code blocks for this blog. Yesterday, I decided to move all the code into GitHub Gist and inject them from there. Using a WordPress plugin to render code blocks can be problematic when update happens. Plugins might not be up to date. It …