Connect Clients

3 min

Data Distiller allows you to connect from a variety of desktop client applications. Data Distiller uses the PostgreSQL protocol, so you dive deeper in how to use PostgreSQL tools and drivers to connect and write queries.

For this part of the lab you will need to have PostgreSQL installed on your local machine to be able to complete query.

{% embed url="https://www.postgresql.org/download/" %}

The PostgreSQL CLI will be included in the download and you will use that to connect to Data Distiller through the terminal or cmd on your computer.

To get the login credentials for connecting a client to Data Distiller, go to Queries on the left side navigation and click on Credentials.

You will see 2 credentials presented to you. The one on the right will be used for connecting UI based clients like DbVisualizer, Looker, Power BI, Tableau, and more. The one on the left will be used to connect to Data Distiller through a terminal or CLI.

For this exercise you will copy the credentials on the left, then open your terminal of choice. Once your terminal is running, copy and paste the credential in the terminal and in a few moments you will be connected to Data Distiller through a client.

Once you are connected you will see the sandbox that you are connected to. You will be able to run queries from the CLI.

Let's do a test and make sure that you can successfully run a query in the terminal.

SELECT _adobedemoamericas275.crmIdentities.crmId as crmID,
  _adobedemoamericas275.crmIdentities.email as email,
  _adobedemoamericas275.crmProfile.firstName as firstName,
  _adobedemoamericas275.crmProfile.hasRewardsCard as hasRewardCard
FROM retail_crm
WHERE _adobedemoamericas275.crmProfile.loyaltyStatus == 'Gold'
LIMIT 20;

Now that you have confirmed that you can successfully run queries through the client, let's move forward with extending the use case from the query completed in the UI.

SELECT crm._adobedemoamericas275.crmIdentities.crmId as crmID,
  crm._adobedemoamericas275.crmIdentities.email as email,
  crm._adobedemoamericas275.crmProfile.firstName as firstName,
  crm._adobedemoamericas275.crmProfile.hasRewardsCard as hasRewardCard,
  evt._adobedemoamericas275.retailEvents.numItems as numOfItems,
  evt._adobedemoamericas275.retailEvents.points as points,
  evt._adobedemoamericas275.retailEvents.purchase as orderComplete,
  evt._adobedemoamericas275.retailEvents.spend as spend
FROM retail_crm crm
JOIN retails_events evt
ON crm._adobedemoamericas275.crmIdentities.loyaltyId = evt._adobedemoamericas275.identities.loyaltyId
WHERE crm._adobedemoamericas275.crmProfile.loyaltyStatus == 'Gold'
and evt._adobedemoamericas275.retailEvents.purchase == 'false';

Now that you have that list, create a new table.

CREATE TABLE cartAbandonment AS
  SELECT crm._adobedemoamericas275.crmIdentities.crmId as crmID,
    crm._adobedemoamericas275.crmIdentities.email as email,
    crm._adobedemoamericas275.crmProfile.firstName as firstName,
    crm._adobedemoamericas275.crmProfile.hasRewardsCard as hasRewardCard,
    evt._adobedemoamericas275.retailEvents.numItems as numOfItems,
    evt._adobedemoamericas275.retailEvents.points as points,
    evt._adobedemoamericas275.retailEvents.purchase as orderComplete,
    evt._adobedemoamericas275.retailEvents.spend as spend
  FROM retail_crm crm
  JOIN retails_events evt
  ON crm._adobedemoamericas275.crmIdentities.loyaltyId = evt._adobedemoamericas275.identities.loyaltyId
  WHERE crm._adobedemoamericas275.crmProfile.loyaltyStatus == 'Gold'
  and evt._adobedemoamericas275.retailEvents.purchase == 'false';

Congrats, you have completed queries using the Client Connector in Data Distiller.