Studio enables you to use the database connectors to connect to your relational databases as well as cloud based services like MySQL, PostgreSQL and Microsoft SQL, MongoDB, Athena AWS, Amazon Redshift and so on.
Let us now understand how to use the Athena connector. Athena AWS is basically an interactive query service with a Schema that makes it easy to start querying using the standard queries easy and fast.
Configuring Athena connector
To add third party DB connectors, under Studio > Connectors, click (+) Connector and select Amazon Athena.
When you are configuring the Connector for Amazon Athena you would have a couple of configurations and permissions to be taken care of.
The administrator of Amazon Athena first needs to set these permissions required to run Athena:
- Set Amazon S3 locations where the underlying data for the query is stored. For more information, you can refer these Identity and access management from the Amazon S3 in the Amazon Simple Storage Service Developer Guide.
- Set metadata and resources that you store in the AWS Glue Data Catalog, such as databases and tables, including additional actions for encrypted metadata. For more information, refer this article for Setting up IAM Permissions for AWS Glue and Setting Up Encryption in AWS Glue in the AWS Glue Developer Guide.
Get AWS Credentials
First, you need to get the AWS Access Key and AWS Secret Key and also make sure that you store it safely for future reference. If you are unable to find them, then you can create a new pair of keys via the AWS console under the My Security Credentials section of the AWS console in the top-right corner of your screen.
- Click AWS Console > My Security Credentials > Access Keys > Create New Access Key
When creating the access key, you can either use the AWS managed policy for “AthenaFullAccess”, or if you prefer, you can use a template to specify the policy to restrict access.
After you have the Security Credentials, you can then configure the Connector.
The most important part of the process is to provide the Connection strings. You would now need to add the Connection string fields required for establishing a database connection or provide the respective connection parameters required, namely AWS Region, S3 output location, Data source, Data source, AWS Access Key Id, AWS Secret Key. You can also enable further action to Whitelist IP.
Once these configurations are done, you need to Test connection. If the authentication is successful you would get the response accordingly. You can now click to Save your configuration.
Adding queries to the connector
Now that you have configured the connector you will find it under the Custom Database connectors list. Now to fetch data or undertake any other action, click the Add query option available once your connection is ready.
Here you can add your queries - simple as well as complex ones. To the right-hand side of the screen, you can see the list of tables from the database. You can expand the tables to view the fields from the table.
You can provide a name to the query using Enter a Service name. Add your query and click Run or press Ctrl + enter to run the query. The rows returned from the table are shown in the Response section.
You can also add dynamic values using the Variables. To add the variable, click Variables > +Add. Add the FieldName and select the Field type. Ensure that you select the field type that matches the column type of the table, so that the query works correctly. Add a Test Value to check the results before saving. You can also add a Default value or make it Mandatory. Add the Help text that would be shown with the field. You can also add any preset formula. Once you have entered the details, click Add Variable. The variable would be listed under Variables. You can make use of the variable in the query where you created the variable.
To use a variable inside a query, you simply need to put it into double Curly brackets. The Test value that you entered would be considered for fetching data.
You can now view the queries that you saved for use in your apps later under your specific connector under Custom Database connectors.
Using Athena connector
Fetch all rows and apply pagination
Now consider an example where we want to find the author names from the table. Let us first fetch data and apply pagination using the Athena Connector. So click Add Query to go to the Query Editor. Here you would add the query with the OFFSET clause.
Run the query to view the results. You can add a dynamic variable if you want to define the OFFSET clause value to specify the number of rows for pagination. Save the query. Your connector is now configured.
Now whenever you want to make use of these Database connectors in your Apps, Under Studio Builder, click Connectors > + Add. Select your ready connector and click Continue. Now from the connector select the query for pagination and click Continue. Now from the connector select the query for pagination and click Continue. Select your database environment. Based on the query selected, you would have the connector fields that would be used in the BINDAPI formula. Add the connector name and other details and click Finish.
In this example form, to bind the fetched data to the Tablegrid control need to use BINDAPI formula either by adding it to the Custom Formula or through Data > Connectors > Dropdown selection (API name and keys) as shown below. You can also view the SQL query being used using Show query. If you want to hide the query for some reason, click Hide Query. Beyond the configured queries you feel the need a few more queries you can use the Add Query option.
Fetch count of rows satisfying a condition
Now that Athena is primarily useful for analytics you can have queries where you want to find the subscribers or visitors to a particular article that is selected from the Tablegrid and then display it as a count in a numeric control on the click of the Action button. In that case you can specify the query accordingly as seen below to get the count. You will have to use a variable to pass the selected URL to the query.
Now in the App builder, first add the Connector and respective query. Then add an action flow that would be trigger with the click of the Action button (named Show Count).
Return the count using a Response variable.
You can then display it by setting the value for the respective numeric control that is added for the purpose.
Using the DB Connector for Athena is similar to any other SQLs like PostgreSQL, MySQL and so on. You can make use of the Queries to fetch data as well bind data such that you can undertake the insert, update, delete actions as well. You need to ensure the syntax which would vary a bit for different SQL systems.