Gives full play to the value of your data
Are you finding yourself overwhelmed with raw data, struggling to discover the insights you need? Are your decisions based more on instinct rather than proves? Your solution is right here — Powerdrill launches its new feature Advanced Analytics.
Advanced Analytics can generate visualizations such as charts and maps after analyzing raw data. With Advanced Analytics, all you need to do is to “tell” Advanced Analytics what you want in natural language and let it uncover the trends and patterns in your data.
We are excited to announce our extension on this feature — SQL Advanced Analytics. This powerful new tool allows you to seamlessly integrate your SQL databases as data sources. Once connected, Powerdrill empowers you to delve into your data with the full suite of SQL commands, unlocking sophisticated analytical possibilities.
Sign in to Powerdrill.
Select Advanced Analytics and select the files you want to analyze.
Only CSV, TSV, and Excel files are supported. Up to 10 files can be uploaded at a time.
Chat with Powerdrill to start your conversation over your dataset.
If you just want to see the effect of Advanced Analytics before uploading your own data, you can try our demo to take a glimpse.
To use SQL Advanced Analytics, you must first connect your SQL databases to Powerdrill. Currently, Powerdrill supports two types of SQL databases: MySQL and PostgreSQL.
Sign in to Powerdrill.
In the left sidebar, select Datasets.
On the page that is displayed, click + Dataset in the upper-right corner.
On the Create Dataset page, click the SQL Databases tab.
If this is your first time using this feature, the platform will prompt you to specify a user with read-only access to your database. For details about how to create a user with read-only privileges on the database, see Set user privileges to read-only.
Select your SQL database type.
For PostgreSQL-compatible databases, select PostgreSQL.
For MySQL-compatible databases, select MySQL.
Configure credentials.
If you choose the General
form, you need to specify the hostname, port, and name of the database to connect.
If you choose the Advanced
form, fill in the domain name of the database to connect to complete the URL.
Configure authentication information, including the username and password used to connect to the database.
If the database is configured with IP allowlists or blocklists, ensure the IP addresses provided on the page are in the allowlists.
Click Test Connection. If the connection is successful, click Finish.
Now, you can ask questions about anything you want to know from your database.
The detailed procedure varies with your database type:
Connect to your PostgreSQL database as a superuser or a user with the necessary privileges to create roles and assign permissions.
For example, run the following command in the psql
CLI:
Replace postgres
with your superuser or administrative username.
Run CREATE ROLE
to create a role.
Replace readonly_user
with the desired username and password
with a strong password. Alternatively, you can run CREATE USER
as a shortcut for CREATE ROLE ... WITH LOGIN
.
Grant the CONNECT
privilege on the database to the user.
Grant the USAGE
privilege on the schema where your data resides.
Grant the SELECT
privilege on the tables in the schema.
Set default privileges to allow readonly_user
has the SELECT
privilege on tables future created in the schema.
Check whether the user privileges are configured as expected in psql
:
Connect to your PostgreSQL database as a superuser or a user with the necessary privileges to create roles and assign permissions.
For example, run the following command in the psql
CLI:
Replace postgres
with your superuser or administrative username.
Run CREATE ROLE
to create a role.
Replace readonly_user
with the desired username and password
with a strong password. Alternatively, you can run CREATE USER
as a shortcut for CREATE ROLE ... WITH LOGIN
.
Grant the CONNECT
privilege on the database to the user.
Grant the USAGE
privilege on the schema where your data resides.
Grant the SELECT
privilege on the tables in the schema.
Set default privileges to allow readonly_user
has the SELECT
privilege on tables future created in the schema.
Check whether the user privileges are configured as expected in psql
:
Use a MySQL CLI to connect to your MySQL server as the root user or another user with sufficient privileges to create new users and grant permissions.
Following uses the root
user and the mysql
CLI as an example.
Enter the password as prompted.
Create a user and set a password for the user.
readonly_user
is an example only. Replace it as needed.localhost
with %
.Grant the SELECT
privilege on all tables in the database to the readonly_user
user.
Make the privileges take effect.
Check whether the user privileges are configured as expected in mysql
: