Connecting to Gmail
The Sisense Gmail connector is a certified connector that allows you to import data from the Gmail API into Sisense via theSisense generic JDBC connector. The Gmail connector offers the most natural way to integrating Gmail capabilities such as sending and receiving email, managing Gmail folders messages, and more, in Java applications.
Sisense Gmail connector is a certified connector. The support for the connector is provided by Sisense and will be assisted by the certification partner's support, if needed. For any support issues or additional functionality requests, please contact your Sisense representative or open a request through our Help Center. For advanced inquiries specific to driver functionality, you can also contact our certification partner’s support directly via [email protected].
After you have downloaded the driver, you can connect through a connection string in Sisense. The connection string is used to authenticate users who connect to the Gmail APIs. To obtain a connection string, you will need to register a Gmail app. Once you have connected to Gmail, you can import a variety of tables from the Gmail API
This page describes how to download the Gmail driver and deploy it, how to connect to Gmail with a connection string, provides information about the Gmail data model, and more.
- Downloading the Gmail JDBC Driver
- Deploying the Gmail JDBC Driver
- Connecting to Gmail
- Adding Gmail Tables to your ElastiCube
- Gmail Connector: Additional Resources
You can download the Gmail JDBC driver here.
- The driver is certified for Sisense v7.2 and above.
- Sisense v7.4 and above: Click the above link to download a ready-to-use driver.
- Sisense prior to v7.4: Click the above link to download a 30-days free-trail of the driver. Contact Sisense for the full license version.
To run the setup, execute the following command: java -jar setup.jar (OR, if your system is set up to run Java applications, double-click on setup.jar).
During the installation, pay attention to the path of the installation (you will need it later on, to direct Sisense to the Jar file. The default path is C:\Program Files\CData\CData JDBC Driver for Gmail 2019\lib).
Note: The install file (setup.jar) is a Java application that requires Java 6 (J2SE) or above to run. If you do not have Java 6 installed, you may download it from here.
The driver communicates with Gmail through the IMAP protocol. IMAP enables all your client devices to work with the same remote data, instead of individual copies. To connect to Gmail, you will first need to enable client access to Gmail over IMAP.
Then, to access Gmail from Sisense, you must provide valid Oauth Gmail credentials through a connection string. These credentials are provided by Gmail when you create an application.
After you receive your credentials from Gmail, you can create the connection string and provide Sisense with it to connect to your data.
Follow the steps below to enable access to Gmail over IMAP:
- Open the Gmail Web interface and click , then select the Settings option.
- On the Forwarding and POP/IMAP tab, select Enable IMAP.
- Save your changes.
Registering an App
Follow the steps below to obtain the OAuth client credentials, the OAuthClientId and OAuthClientSecret necessary for your connection string:
- Log into the Google API Console.
- Click Create Project or select an existing project.
- In the API Manager, click Credentials > Create Credentials > OAuth Client Id.
- Select the application type.If you are making a Web application, select Web application. In the Authorized Redirect URIs box, enter the URL you want to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. If you are making a desktop application, select Other.
- Click Create. The OAuthClientId and OAuthClientSecret are displayed.
- Click Library > Gmail API > Enable API.
Creating the Gmail Connection String
Sisense uses connection strings to connect to Gmail and import data into Sisense.
The following is an example of a Gmail connection string:
The connection string should include the following information:
- InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
- OAuthClientId: Set this to the Client ID in your app settings.
- OAuthClientSecret: Set this to the Client secret in your app settings.
- User: Set this to the user of the Gmail account.
When you connect the driver opens the OAuth endpoint in your default browser. Log in and grant permissions to the application.
To help you create a connection string and test the connection, see Connection String Builder for Certified Connectors.
If you have any issues connecting to your data source, see Troubleshooting JDBC Data Connectors.
- In the Data page, open an ElastiCubeor create a new ElastiCube.
- In the Model Editor, click . The Add Data dialog box is displayed.
- Click Generic JDBC to open the JDBC settings.
- In Connection String, enter the Gmail URL. See Creating a Connection String for more information.
- In JDBC JARs Folder, enter the name of the directory where the Gmail JAR file is located (see Deploying the Gmail JDBC Driver).
- In Driver's Class Name, enter the following class name: cdata.jdbc.gmail.GmailDriver.
- In User Name and Password, enter your Gmail credentials. These fields are not required if the user name and password were provided in the connection string.
- Click Next. A list of tables in the database are displayed. All tables and views associated with the database will appear in a new window.
- From the Tables list, select the relevant table or view you want to work with. You can click next to the relevant table or click Preview to see a preview of the data inside it.
- (Optional) Click + to customize the data you want to import with SQL. See Importing Data with Custom Queries for more information.
- After you have selected all the relevant tables, click Done. The tables are added to your data model.
For the full documentation set for the Gmail connector, click here.
For connection string options, click here.
For information on the Gmail data model, click here.
- The default Gmail table names contain a backslash; some also contain spaces. You can use square brackets to escape the table names for the default Gmail folders. For example: SELECT * FROM [Gmail/All Mail]
- The default number of messages returned is 100. This can be changed by setting either LIMIT or MaxItems. If you wish to return all mail within a mailbox, specify a value of 0 or -1.
- Aggregate functions not supported.