Snowflake Federated Agent
Intro
The Domo Federated Agent is a solution that allows you to connect your on-premise data to Domo through Snowflake. The agent makes a socket connection to Domo and then proxies requests to your database which allows you to visualize your snowflake data in Domo in real-time, any time, anywhere. With the Snowflake Federated Agent, your data always stays in your database and is never stored in Domo.
How to get the Federated Agent
For more information on the Snowflake Federated Agent pricing and access, contact your Customer Success Manager (CSM).
Prerequisites
-
Admin access to your database and the ability to write a new table to your database.
-
Database access credentials
-
Admin level access in Domo
-
Database connection URL
-
(Optional) Domo Access Token
Agent Configuration
To properly configure your Domo Federated Agent,
-
Navigate to the conf directory in the unzipped file Domo provided.
-
Open the agent configuration file titled agentConfig.yml
Note: The configuration files are all .yml files are case and character sensitive. -
Fill in your Domo domain.
-
Uncomment the
adapterType: snowflake
andconfigurationFileName: snowflakeAdapterConfig.yml
lines.
There are two ways to authenticate to Domo—using a developerToken property (recommended) or using Oauth2 (default). Using a developerToken is recommended because it automatically authenticates to Domo every time. By using Oauth2, for every change or restart of the agent, you will need to reauthorize.
To authenticate using a developerToken,
-
Create an Access Token in Domo. See Managing Access Tokens for detailed instructions on creating an Access Token.
-
Uncomment the developerToken line and enter the Access Token.
To authenticate using Oauth2,
No additional configuration is needed. Leave the developerToken line commented out, and the agent will prompt you to authenticate by Oauth login.
Adapter Configuration
-
In the conf directory of the unzipped file Domo provided, open the snowflakeAdapterConfig.yml file.
-
Enter the required information on the uncommented lines.
For every table you wish to visualize in Domo, the tableName:
line must be copied.
Password Encryption
In order to run the Snowflake Federated Agent, you will need to set a password. Optionally, your password can be used to encrypt your database passwords and Domo Access Token.
If you choose not to encrypt, you still need to generate a password and store it in one of two ways.
Set the password as an Environment Variable
Variable: DOMO_FD_ENCRYPTION_PW
E.g. DOMO_FD_ENCRYPTION_PW=<your encryption password>
Pass it as a command line property
If this method is chosen, you must edit the startAgent.sh or startAgent.bat file, found in the bin directory, and add the -p=<yourPassword> property at the end of the java -jar line. Using this method, the password will be stored in the command history.
E.g. java -jar libs/federated-agent.jar -p=testpw
Optionally, the database passwords and Domo Auth Token can be encrypted using our encryption jar. You can also place your passwords in plain text in the configuration files.
To encrypt your passwords or token run the startEncryption.sh or startEncryption.bat file found in the bin folder and follow the instructions there. Copy the newly encrypted database password and store it in the appropriate adapter configuration.
Standard Calendar Table Configuration
In order for date grain queries to work in Domo, the domo.standardcalendar must be loaded into your database.
Important:
-
If the Standard Calendar Table is not configured, any query that includes a date type column will fail.
-
Domo uses double quotes for identifiers in SQL queries, which may result in queries containing identifiers that cannot be found in Snowflake due to differences in case. To prevent this situation from occurring, Snowflake provides a session parameter, QUOTED_IDENTIFIERS_IGNORE_CASE, that ensures all alphabetical characters in identifiers created within the session are stored in uppercase, regardless of whether the identifier is double-quoted. You can fix any problems with this by setting the session parameter at the user level using the ALTER USER command. The syntax is as follows:
ALTER USER [ IF EXISTS ] <name> SET { [ objProperties ] [ sessionParams ] }
For more information, see https://docs.snowflake.net/manuals/sql-reference/parameters.html#quoted-identifiers-ignore-case.
To configure the Standard Calendar table,
-
Navigate to the standardCalendar folder of the unzipped file provided by Domo.
-
Select the snowflake folder. There are create table statements listed under each database type in the format
<databaseName>Standardcalendar.sql
.Note: For each database used, a standard calendar table using the schema below will need to be created. -
Once the table is created, load the data using either the standardcalendar_data.csv or standardcalendar_data.txt file found in the standardCalendar file.
Note: Fields are terminated with the ',' character and lines are terminated with the '\r\n' return. There are no quotes surrounding any of the data, and no need to escape any fields. The dates are in the format "yyyy-MM-dd". -
Optionally, there is a standard_calendar_data.sql file where the data is formatted as insert statements.
domo.standardcalendar Schema
Field Name | Data Type |
---|---|
Domo_Key | Date |
Date | Date |
DayOfWeek | String |
DayNumberInWeek | Integer |
DayNumberInMonth | Integer |
DayNumberInQuarter | Integer |
DayNumberInYear | Integer |
MonthName | String |
MonthAbrv | String |
MonthNumberInYear | Integer |
Quarter | String |
Year | Integer |
CalendarMonth | String |
CalendarQuarter | String |
WeekStart | Date |
QuarterStart | Date |
DaySinceEpoch | Integer |
WeekSinceEpoch | Integer |
MonthSinceEpoch | Integer |
QuarterSinceEpoch | Integer |
YearSinceEpoch | Integer |
MonthStart | Date |
YearStart | Date |
QuarterNumberInYear | Integer |
Fiscal Calendar Configuration
If a fiscal calendar needs to be set up for Federated Data, please send an email to labs@domo.com including the following items:
-
Domo domain
Then, we will provide you with the schema and steps for adding the calendar to your database.
Running the Agent
Once all components above are properly configured, it is time to run the agent.
To run the Agent,
-
Using the appropriate startAgent file for your machine from your bin folder found in the Domo provided file.
-
For Windows, use the .bat file.
-
For Mac or Linux, use the .sh file.
-
-
Make sure a DataSet was created in Domo for your tables by navigating to the Data Center in Domo.
-
Filter to the DataSet type of Data Anywhere to see any federated queried DataSets.
-
You can now use your federated DataSets in Domo to create cards and consume your on-premise data.
FAQ
Can I still use PDP and create Beastmodes on federated DataSets?
Yes, you can still use PDP and create Beastmodes just like on a standard DataSet.
Can I use my federated DataSets in Domo DataFlows?
No, federated DataSets are not supported as inputs in Magic ETL, MySQL Dataflows, or Fusion.
Can I set Alerts on cards powered from a federated DataSet?
No, Alerts are not currently supported on cards powered by a federated DataSet.