Omnata Product Documentation
  • Omnata Sync for Snowflake
    • What is Omnata Sync for Snowflake?
    • How it works
      • Terminology
      • Branching Mode
      • Sync Directions and Strategies
        • Outbound
        • Inbound
      • Rate Limiting
      • Access Control
      • Notifications
      • Security and Privacy
      • Internal tables
      • Internal Stored Procedures
      • FAQ
    • Step-by-step guides
      • 1. Install the Omnata Sync Engine
      • 2. Install the Omnata Plugin
      • 3. Configure the Plugin
      • 4. Create a connection
      • 5. Create a sync
    • Apps
      • Aerobotics
        • 📘Release History
      • Airtable
        • 📘Release History
      • Amazon Ads
        • Privacy Notice
      • ApprovalMax
        • 📘Release History
      • Bamboo HR
        • 📘Release History
      • Clockify
        • 📘Release History
      • Contentful
        • 📘Release History
      • GitHub
        • 📘Release History
      • Github
      • Google Ads
        • 📘Release History
      • Google Sheets
        • 📘Release History
      • HubSpot
        • 📘Release History
      • Hubspot
      • Infor Data Lake
        • 📘Release History
      • Jira
        • 📘Release History
      • LinkedIn Ads
        • 📘Release History
      • Mailgun
        • 📘Release History
      • Marketo
        • 📘Release History
      • Meta Marketing
        • 📘Release History
      • Microsoft Ads
        • 📘Release History
      • Microsoft Dynamics 365 Business Central
        • 📘Release History
        • 📘Release History
        • 📘Release History
        • 📘Release History
        • 📘Release History
        • 📘Release History
      • Microsoft Entra ID
        • 📘Release History
        • 📘Release History
        • 📘Release History
        • 📘Release History
      • Microsoft Excel
      • Microsoft SQL Server
        • 📘Release History
      • Monday.com
        • 📘Release History
      • MRPeasy
        • 📘Release History
      • PayHero
        • 📘Release History
      • Pinterest Ads
        • Privacy Policy
      • PostgreSQL
        • 📘Release History
      • Salesforce
        • Salesforce Permissions needed
        • Formula Fields
        • How we use the Salesforce APIs
        • 📘Release History
      • Salesforce Marketing Cloud
        • OAuth for APIs, SFTP for file transfer with GPG on outbound
        • OAuth for APIs, SFTP for file transfer
        • OAuth for APIs, Cloud Storage for file transfer
        • 📘Release History
      • Shopify
        • Outbound sync data structures
        • 📘Release History
      • Slack
        • 📘Release History
      • Tiktok Ads
        • Privacy Policy
      • Typeform
        • 📘Release History
      • Wise
        • 📘Release History
      • Xero
        • 📘Release History
      • Zendesk Support
        • 📘Release History
        • 📘Release History
        • 📘Release History
    • Plugins
      • Anatomy of a Plugin
      • Example Plugins
        • Example Plugin: Slack
        • Example Plugin: Zoho CRM
      • Creating Plugins
      • Advanced Plugin topics
        • Advanced rate limiting / concurrency
        • Custom Jinja filters
        • Custom Record Transformers
        • Dynamic Configuration Forms
        • Test case generation
    • Branching
      • Inbound Sync branching
      • Outbound Sync branching
    • Integrations
      • dbt
        • Validation Tests (coming soon)
    • 📘Release History
  • Omnata Connect for Salesforce
    • Overview
    • Getting Started
      • Install the Salesforce App
      • Connect to your data warehouse
        • Snowflake
        • BigQuery
        • Rockset
        • Firebolt
        • SingleStore (previously MemSQL)
      • Deciding which mode to use
    • Omnata with Salesforce Connect (External Objects)
      • Object Configuration
      • View your data in a list
      • Link to other objects
      • Use in a Report
      • Database schema changes
      • Writing to External Objects
    • Omnata with Salesforce Lightning Components
      • Object Configuration
      • View your data in a list
      • Link to other objects
      • Using the Lightning Component on a page
      • Assigning Permissions
    • Advanced Features
      • Row Level Filtering
      • Multi-Currency handling
        • About Multi-Currency
        • Support in Omnata Connect
        • Apex Features
    • Integrations
      • Datadog
    • Omnata with Salesforce Apex
    • Security
    • Use cases
      • Linked object drill-downs
      • Global Search
      • ERP and historical data
      • Embedded product metrics
    • Best Practices
      • Global Search
      • Change Management
      • Snowflake table design
      • Salesforce page layout
      • Salesforce Caching
Powered by GitBook
On this page
  • Querying data from custom Apex code
  • Prerequisites
  • Example code (New Snowflake API)
  • Example code (Old Snowflake API)
  • Exceptions
  • Mocking for tests
  1. Omnata Connect for Salesforce

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.

The Apex interface is closer to the wire than Salesforce Connect, so different product APIs return different classes.

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 (New Snowflake API)

omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection('Snowflake');

List<omnata_sf.SnowflakeQueryBinding> queryBindings = new List<omnata_sf.SnowflakeQueryBinding>();
String testString = 'A000001';
queryBindings.add(new omnata_sf.SnowflakeQueryBinding(testString));

omnata_sf.SnowflakeApiQueryResponse response = connection.executeQueryV2('select * from FINANCIAL_TRANSACTIONS where ACCOUNT_NUMBER=?',queryBindings);

Integer totalRows = response.resultSetMetaData.numRows;

List<List<String>> rowset = response.data;
List<omnata_sf.SnowflakeApiQueryResponse.Rowtype> rowtypes = response.resultSetMetaData.rowtype;

// If you don't need type conversion, this is the string representation straight from Snowflake
List<String> firstRow = rowset.get(0);
String firstColumnValueOfFirstRow = firstRow.get(0);

// Use this option to get a list of rows, with columns indexed by name
List<Map<String,Object>> allRowsWithNamedColumns = response.getAllRowsWithNamedColumns();

// Use this option to get a list of rows, with columns indexed by position
List<List<Object>> allRowsWithOrdinalColumns = response.getAllRowsWithOrdinalPositions();

Example code (Old Snowflake API)

omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection('Snowflake');

List<omnata_sf.SnowflakeQueryBinding> queryBindings = new List<omnata_sf.SnowflakeQueryBinding>();
String testString = 'A000001';
queryBindings.add(new omnata_sf.SnowflakeQueryBinding(testString));

omnata_sf.SnowflakeSchemaResponse response = connection.executeQuery('select * from FINANCIAL_TRANSACTIONS where ACCOUNT_NUMBER=?',queryBindings);

Integer totalRows = response.data.total;

List<List<String>> rowset = response.data.rowset;
List<omnata_sf.SnowflakeSchemaResponse.Rowtype> rowtypes = response.data.rowtype;

// If you don't need type conversion, this is the string representation straight from Snowflake
List<String> firstRow = rowset.get(0);
String firstColumnValueOfFirstRow = firstRow.get(0);

// Use this option to get a list of rows, with columns indexed by name
List<Map<String,Object>> allRowsWithNamedColumns = response.getAllRowsWithNamedColumns();

// Use this option to get a list of rows, with columns indexed by position
List<List<Object>> allRowsWithOrdinalColumns = response.getAllRowsWithOrdinalPositions();

Exceptions

The followings exceptions may be thrown by the executeQuery method:

  • ExternalObjectException if Snowflake returns an error message

  • omnata_sf.UnauthorizedException if 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.

New Snowflake API

omnata_sf.SnowflakeApiQueryResponse mockResponse = new omnata_sf.SnowflakeApiQueryResponse();
mockResponse.resultSetMetaData = new omnata_sf.SnowflakeApiQueryResponse.ResultSetMetaData();
mockResponse.resultSetMetaData.numRows = 2;
mockResponse.resultSetMetaData.rowtype = new List<omnata_sf.SnowflakeApiQueryResponse.Rowtype>();
omnata_sf.SnowflakeApiQueryResponse.Rowtype row1 = new omnata_sf.SnowflakeApiQueryResponse.Rowtype();
row1.name = 'Column1';
row1.type = 'VARCHAR';
omnata_sf.SnowflakeApiQueryResponse.Rowtype row2 = new omnata_sf.SnowflakeApiQueryResponse.Rowtype();
row2.name = 'Column2';
row2.type = 'NUMBER';
mockResponse.resultSetMetaData.rowtype.add(row1);
mockResponse.resultSetMetaData.rowtype.add(row2);
// data is a 2D matrix of the raw values Snowflake returns
mockResponse.data = new List<List<String>>();
mockResponse.data.add(new List<String>{'AColumn1Value','123.45'});
mockResponse.data.add(new List<String>{'AnotherColumn1Value','456.78'});

// pass in the mock object, instead of a connection name
omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection(mockResponse);
// execute a query to get your own response object back
omnata_sf.SnowflakeApiQueryResponse response = connection.executeQueryV2('doesnt matter what goes here, not sent to Snowflake',null);

Integer totalRowsInt = response.resultSetMetaData.numRows;
System.assertEquals(2,totalRowsInt);

Old Snowflake API

omnata_sf.SnowflakeSchemaResponse mockResponse = new omnata_sf.SnowflakeSchemaResponse();
mockResponse.data = new omnata_sf.SnowflakeSchemaResponse.Data();
mockResponse.data.rowtype = new List<omnata_sf.SnowflakeSchemaResponse.Rowtype>();

// Add column metadata
omnata_sf.SnowflakeSchemaResponse.Rowtype firstColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype();
firstColumn.name = 'Column1';
firstColumn.type_Z = 'VARCHAR'; // This is the Snowflake column's native data type
firstColumn.scale = 100;
mockResponse.data.rowtype.add(firstColumn);

omnata_sf.SnowflakeSchemaResponse.Rowtype secondColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype();
secondColumn.name = 'Column2';
secondColumn.type_Z = 'FIXED';
firstColumn.scale = 10;
firstColumn.precision = 2;
mockResponse.data.rowtype.add(secondColumn);

omnata_sf.SnowflakeSchemaResponse.Rowtype thirdColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype();
thirdColumn.name = 'Column3';
thirdColumn.type_Z = 'BOOLEAN';
mockResponse.data.rowtype.add(thirdColumn);

omnata_sf.SnowflakeSchemaResponse.Rowtype fourthColumn = new omnata_sf.SnowflakeSchemaResponse.Rowtype();
fourthColumn.name = 'Column4';
fourthColumn.type_Z = 'TIMESTAMP_LTZ';
mockResponse.data.rowtype.add(fourthColumn);

// add row values, these are always just a 2D matrix of the raw values Snowflake returns
DateTime dt1 = DateTime.newInstanceGmt(2017,4,9,0,0,0);
DateTime dt2 = DateTime.newInstanceGmt(2019,10,9,5,30,15);

mockResponse.data.rowset = new List<List<String>>();
mockResponse.data.rowset.add(new List<String>{'AColumn1Value','123.45','0',(dt1.getTime()/1000)+''}); // Snowflake returns epoch seconds
mockResponse.data.rowset.add(new List<String>{'AnotherColumn1Value','456.78','1',(dt2.getTime()/1000)+''});
mockResponse.data.total=2;

// pass in the mock object, instead of a connection name
omnata_sf.SnowflakeConnection connection = new omnata_sf.SnowflakeConnection(mockResponse);
// execute a query to get your own response object back
omnata_sf.SnowflakeSchemaResponse response = connection.executeQuery('doesnt matter what goes here, not sent to Snowflake',null);

Integer totalRowsInt = response.data.total;
System.assertEquals(2,totalRowsInt);
List<List<String>> rowset = response.data.rowset;
List<omnata_sf.SnowflakeSchemaResponse.Rowtype> rowtypes = response.data.rowtype;

// Use this option to get a list of rows, with columns indexed by name
List<Map<String,Object>> allRowsWithNamedColumns = response.getAllRowsWithNamedColumns();

System.assertEquals('AColumn1Value',allRowsWithNamedColumns.get(0).get('Column1'));
System.assertEquals(123.45,allRowsWithNamedColumns.get(0).get('Column2'));
System.assertEquals(0,allRowsWithNamedColumns.get(0).get('Column3'));
System.assertEquals(dt1,allRowsWithNamedColumns.get(0).get('Column4'));

System.assertEquals('AnotherColumn1Value',allRowsWithNamedColumns.get(1).get('Column1'));
System.assertEquals(456.78,allRowsWithNamedColumns.get(1).get('Column2'));
System.assertEquals(1,allRowsWithNamedColumns.get(1).get('Column3'));
System.assertEquals(dt2,allRowsWithNamedColumns.get(1).get('Column4'));

System.debug('Completed successfully');
PreviousDatadogNextSecurity

Last updated 11 months ago