Querying Snowflake from custom Apex code

Snowflake Connection

Ensure that package installation and initial Snowflake configuration has been performed.

Once complete, Snowflake data can be queried via custom Apex code.

Example code

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 totalRowsInt = 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.SnowflakeUnauthorizedException if Snowflake indicates that the credentials are not valid