Omnata Product Documentation
Search…
Omnata with Salesforce Apex
Querying data from custom Apex code
Omnata provides a native Apex interfaces to your data warehouse. This allows a Salesforce developer to query external data as needed, without needing to build and maintain a custom API integration, or handle the various data type conversions.
Only Snowflake is supported.
Prerequisites
Ensure that package installation and initial connection configuration has been performed.
Once complete, data can be queried via custom Apex code by referencing the connection by name.
Example code
1
omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection('Snowflake');
2
3
List<omnata_sf.SnowflakeQueryBinding> queryBindings = new List<omnata_sf.SnowflakeQueryBinding>();
4
String testString = 'A000001';
5
queryBindings.add(new omnata_sf.SnowflakeQueryBinding(testString));
6
7
omnata_sf.SnowflakeSchemaResponse response = connection.executeQuery('select * from FINANCIAL_TRANSACTIONS where ACCOUNT_NUMBER=?',queryBindings);
8
9
Integer totalRowsInt = response.data.total;
10
11
List<List<String>> rowset = response.data.rowset;
12
List<omnata_sf.SnowflakeSchemaResponse.Rowtype> rowtypes = response.data.rowtype;
13
14
// If you don't need type conversion, this is the string representation straight from Snowflake
15
List<String> firstRow = rowset.get(0);
16
String firstColumnValueOfFirstRow = firstRow.get(0);
17
18
// Use this option to get a list of rows, with columns indexed by name
19
List<Map<String,Object>> allRowsWithNamedColumns = response.getAllRowsWithNamedColumns();
20
21
// Use this option to get a list of rows, with columns indexed by position
22
List<List<Object>> allRowsWithOrdinalColumns = response.getAllRowsWithOrdinalPositions();
23
Copied!
Exceptions
The followings exceptions may be thrown by the executeQuery method:
ExternalObjectExceptionif Snowflake returns an error messageomnata_sf.UnauthorizedExceptionif the connection fails due to invalid credentials.
Mocking for tests
The containers that carry API responses are global classes, so that they can be mocked.
Below is an example Apex script where the response to a simple Snowflake query is mocked.
1
omnata_sf.SnowflakeSchemaResponse mockResponse = new omnata_sf.SnowflakeSchemaResponse();
2
mockResponse.data = new omnata_sf.SnowflakeSchemaResponse.Data();
3
mockResponse.data.rowtype = new List<omnata_sf.SnowflakeSchemaResponse.Rowtype>();
4
5
// Add column metadata
6
omnata_sf.SnowflakeSchemaResponse.Rowtype firstColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype();
7
firstColumn.name = 'Column1';
8
firstColumn.type_Z = 'VARCHAR'; // This is the Snowflake column's native data type
9
firstColumn.scale = 100;
10
mockResponse.data.rowtype.add(firstColumn);
11
12
omnata_sf.SnowflakeSchemaResponse.Rowtype secondColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype();
13
secondColumn.name = 'Column2';
14
secondColumn.type_Z = 'FIXED';
15
firstColumn.scale = 10;
16
firstColumn.precision = 2;
17
mockResponse.data.rowtype.add(secondColumn);
18
19
omnata_sf.SnowflakeSchemaResponse.Rowtype thirdColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype();
20
thirdColumn.name = 'Column3';
21
thirdColumn.type_Z = 'BOOLEAN';
22
mockResponse.data.rowtype.add(thirdColumn);
23
24
omnata_sf.SnowflakeSchemaResponse.Rowtype fourthColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype();
25
fourthColumn.name = 'Column4';
26
fourthColumn.type_Z = 'TIMESTAMP_LTZ';
27
mockResponse.data.rowtype.add(fourthColumn);
28
29
// add row values, these are always just a 2D matrix of the raw values Snowflake returns
30
DateTime dt1 = DateTime.newInstanceGmt(2017,4,9,0,0,0);
31
DateTime dt2 = DateTime.newInstanceGmt(2019,10,9,5,30,15);
32
33
mockResponse.data.rowset = new List<List<String>>();
34
mockResponse.data.rowset.add(new List<String>{'AColumn1Value','123.45','0',(dt1.getTime()/1000)+''}); // Snowflake returns epoch seconds
35
mockResponse.data.rowset.add(new List<String>{'AnotherColumn1Value','456.78','1',(dt2.getTime()/1000)+''});
36
mockResponse.data.total=2;
37
38
// pass in the mock object, instead of a connection name
39
omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection(mockResponse);
40
// execute a query to get your own response object back
41
omnata_sf.SnowflakeSchemaResponse response = connection.executeQuery('doesnt matter what goes here, not sent to Snowflake',null);
42
43
Integer totalRowsInt = response.data.total;
44
System.assertEquals(2,totalRowsInt);
45
List<List<String>> rowset = response.data.rowset;
46
List<omnata_sf.SnowflakeSchemaResponse.Rowtype> rowtypes = response.data.rowtype;
47
48
// Use this option to get a list of rows, with columns indexed by name
49
List<Map<String,Object>> allRowsWithNamedColumns = response.getAllRowsWithNamedColumns();
50
51
System.assertEquals('AColumn1Value',allRowsWithNamedColumns.get(0).get('Column1'));
52
System.assertEquals(123.45,allRowsWithNamedColumns.get(0).get('Column2'));
53
System.assertEquals(0,allRowsWithNamedColumns.get(0).get('Column3'));
54
System.assertEquals(dt1,allRowsWithNamedColumns.get(0).get('Column4'));
55
56
System.assertEquals('AnotherColumn1Value',allRowsWithNamedColumns.get(1).get('Column1'));
57
System.assertEquals(456.78,allRowsWithNamedColumns.get(1).get('Column2'));
58
System.assertEquals(1,allRowsWithNamedColumns.get(1).get('Column3'));
59
System.assertEquals(dt2,allRowsWithNamedColumns.get(1).get('Column4'));
60
61
System.debug('Completed successfully');
Copied!
Last modified 4mo ago