Motherduck

Using Hydra Postgres, you can leverage MotherDuck without leaving Hydra.

In addition to a generous free tier, MotherDuck has a free trial where you can get started for 30 days without a credit card. To get started, sign up for MotherDuck. Next, you’ll need to generate and retrieve an access token for authentication.

To make Hydra work with MotherDuck, retrieve your motherduck_token and open a support ticket with Hydra. We will connect MotherDuck to your Hydra Postgres database.

​

Create a MotherDuck access token

• Go to the MotherDuck UI
• In top left click on organization name and then Settings
• Click + Create token
• Specify a name for the token that you’ll recognize (like “DuckDB CLI on my laptop”)
• Choose whether you want the token to expire and then click on Create token
• Copy the access token token to your clipboard by clicking on the copy icon

Once you have an access token, contact us and we’ll set up Motherduck for you. Please include for which project and Postgres database you would like Motherduck enabled.

​

Using MotherDuck

Now within your Hydra Postgres database, you can start querying MotherDuck databases or shares. The below query uses a sample_data share database accessible by all MotherDuck users.

-- number of mention of duckdb in HackerNews in 2022
SELECT
    EXTRACT(YEAR FROM timestamp) AS year,
    EXTRACT(MONTH FROM timestamp) AS month,
    COUNT(*) AS keyword_mentions
FROM ddb$sample_data$hn.hacker_news
WHERE
    (title LIKE '%duckdb%' OR text LIKE '%duckdb%')
GROUP BY year, month
ORDER BY year ASC, month ASC;

 year | month | keyword_mentions
------+-------+------------------
 2022 |     1 |                6
 2022 |     2 |                4
 2022 |     3 |               10
 2022 |     4 |                9
 2022 |     5 |               43
 2022 |     6 |                8
 2022 |     7 |               15
 2022 |     8 |                6
 2022 |     9 |               19
 2022 |    10 |               10
 2022 |    11 |                9

You can join your data in MotherDuck with your live data in Hydra Postgres, and you can also easily copy data from one to the other.

For instance, if you create a table by using the USING duckdb keyword it will be created in MotherDuck, and otherwise it will be in Postgres.

Let’s take the same above query using MotherDuck but now creating a Postgres table :

CREATE TABLE hacker_news_duckdb_postgres AS
SELECT
    EXTRACT(YEAR FROM timestamp) AS year,
    EXTRACT(MONTH FROM timestamp) AS month,
    COUNT(*) AS keyword_mentions
FROM ddb$sample_data$hn.hacker_news
WHERE
    (title LIKE '%duckdb%' OR text LIKE '%duckdb%')
GROUP BY year, month
ORDER BY year ASC, month ASC;

If we display the existing tables in PostgreSQL, we’ll see this one stored as PostgreSQL table (Access method is heap).

postgres=# \d+
                                                List of relations
 Schema |            Name             | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+-----------------------------+-------+----------+-------------+---------------+------------+-------------
 public | hacker_news_duckdb_postgres | table | postgres | permanent   | heap          | 8192 bytes |

Now, we can also copy this Postgres table to MotherDuck using:

CREATE TABLE hacker_news_duckdb_motherduck
USING duckdb
AS
SELECT * FROM hacker_news_duckdb_postgres;