Seamless Integration: Connecting PrestoDB to SingleStore for High-Performance Analytics

    By: 

    Saurabh Mahawar, Developer Relations Engineer at IBM

    In today’s data-driven landscape, organization’s are constantly seeking ways to analyze massive datasets quickly and efficiently. PrestoDB, a powerful open-source SQL query engine, and SingleStore, a distributed SQL database, are two technologies that, when combined, offer unparalleled capabilities for high-performance data querying and distributed analytics. This guide provides a hands-on, step-by-step tutorial on how to connect PrestoDB to a SingleStore database, enabling you to leverage the strengths of both platforms.

    By integrating Presto to query SingleStore, you gain Presto’s federated query capabilities alongside SingleStore’s remarkable speed and efficiency. This setup is a game-changer for ad-hoc analysis and real-time reporting.

    Prerequisites ✅

    Before we dive into the connection setup, ensure you have the following ready:

    • PrestoDB Installed: You should have a running instance of PrestoDB. If you need a quick guide on how to install Presto server, check this out.
    • Access to a SingleStore Instance: For this tutorial, we will be using the Standard Self-Managed (Free) Edition of SingleStore.

    Step-by-Step Connection Setup ⚙️

    Let’s walk through the process of connecting PrestoDB to your SingleStore instance.

    1. Create Your SingleStore Workspace and Obtain Credentials.

    After creating your SingleStore account, follow these steps to set up your workspace and retrieve necessary connection details:

    • Click on Ingest ➜ Pipelines to create a workspace. This will allow you to view your workspace details.
    • Next, click on Connect ➜ SQL IDE. Here, you will find the credentials required to connect PrestoDB to SingleStore.

    Important: You will need to reset the password, as it is visible only once. Copy and save these credentials, including the host, port, username, password, and database name, for future use.

    2. Configure the Presto Catalog for SingleStore

    The core of connecting Presto to any data source lies in configuring a catalog. A Presto catalog informs the query engine which data source to connect to.

    • Navigate to your Presto installation directory. Inside, you’ll find the etc/catalog folder
    • Within the catalog folder, create a new file named singlestore.properties. This file will contain the configuration for our SingleStore connection, leveraging the SingleStore connector available in Presto.

    3. Define Connection Properties

    • Open the singlestore.properties file and add the following configuration:
    connector.name=singlestore
connection-url=jdbc:singlestore://svc-3482219c-a389-4079-b18b-d50662524e8a-shared-dml.aws-virginia-6.svc.singlestore.com:3333/client_info?useSSL=true&allowPublicKeyRetrieval=true&trustServerCertificate=true
connection-user=speed-d2ce5
connection-password=<Your_Password>

    Key Properties Explained:

    a. connector.name: This property must be set to singlestore. It instructs Presto to use its built-in SingleStore connector.

    b. connection-url: This is the JDBC URL for your SingleStore cluster.

        ◦ Replace <host> and <port> with your specific SingleStore aggregator host and port.

        ◦ The parameters client_info?useSSL=true&allowPublicKeyRetrieval=true&trustServerCertificate=true are crucial:

    Note: client_info represents the database name you created in SingleStore. For Free Tier accounts, providing a default database name is mandatory. useSSL=true, allowPublicKeyRetrieval=true, and trustServerCertificate=true are mandatory parameters to enable SSL and establish a secure connection with SingleStore.

    c. connection-user: Your SingleStore username.

    d. connection-password: Your SingleStore password. Remember to replace <Your_SingleStore_Password> with the password you saved earlier.

    4. Upload Sample Data (Optional but Recommended)

    To test your connection effectively, it’s helpful to have some data in SingleStore.

    • You can manually upload data in SingleStore using the Load Data option. For example, a customers.csv file was used in the source material.

    5. Restart Presto Server and Verify Connection

    • After saving the singlestore.properties file, restart your Presto server.
    • Now, let’s verify that the singlestore catalog is recognized by Presto by executing the following query:
    Show Catalogs;
    • You should see singlestore in the output, confirming that your catalog has been successfully recognized. If you can then see the list of your SingleStore databases, congratulations! You are now connected and ready to run queries directly through PrestoDB.
    Show schemas from singlestore;
    select * from singlestore.client_info.customers order by customer_id limit 100;

    Troubleshooting Tips 🛠️

    Encountering issues? Here are some common problems and their solutions:

    • Invalid Credentials: Double-check that your connection-user and connection-password in the singlestore.properties file are correct and case-sensitive.
    • No SSL Detected: SingleStore requires SSL for connection to Presto. Ensure all required SSL parameters (useSSL, allowPublicKeyRetrieval, trustServerCertificate) are included in your connection-url. For production environments, it’s recommended to download the CA bundle and save it locally.
    • Free-Tier Limitations: If you’re using the SingleStore Free Tier, remember that you’re typically limited to one database and a maximum of 10 tables. It’s also mandatory to pass a default database name in the connection-url.

    Follow Presto at Linkedin, Youtube, and Join Slack channel to interact with the community.