Tag Archives: sql

How to use CockroachDB with Emacs SQL Mode

old skool

(Image courtesy Sajith T S under Creative Commons license.)

In this post I’ll describe how to hack up the Postgres config for sql.el to work with CockroachDB (aka “CRDB”).

In the future I’d like to submit code to sql.el to make CRDB a fully-supported option, but for now this is what I’ve been using.

Note that these instructions assume you are running a secure local cluster. If you are running insecure, you can avoid all of this and just M-x sql-postgres and use most of the defaults, modulo port numbers and such. It uses psql on the backend which works because CRDB speaks the Postgres wire format.

However, once you get up and running for real and want to use a secure network connection, it’s easier to use the cockroach sql client. That’s what we’ll configure in this post.

(It may be possible to configure psql to use the CRDB certs, but I don’t know since I haven’t looked into it. Also, keep in mind that I have not tested this setup over the network yet – only on my local machine.)

Step 1. Modify basic config

Since the client is invoked by comint as two “words”, cockroach sql, you have to mess with the options a bit.

First set the cockroach binary:

(setq sql-postgres-program "cockroach")

Then invoke the SQL client with the first arg, and pass options in with the rest. The certs directory is where your encryption certificates are stored. Since this is an ephemeral local cluster I’m using the temp directory.

(setq sql-postgres-options
      '("sql" "--certs-dir=/tmp/certs"))

Finally the login params are pretty standard. My local clusters are not usually long-lived, so I just use the “root” user. This would not be recommended on real systems of course.

(setq sql-postgres-login-params
      '((user :default "root")
        (database :default "")
        (server :default "localhost")
        (port :default 26500)))

Step 2. Modify the Postgres “product” to work with CRDB

Sql.el calls each of its supported databases “products”. for whatever reason.

In any case, here’s how to modify the Postgres product to work for CRDB.

First we need a new function to talk to comint.el. (See the bottom of this post for the definition since it’s longer and not interesting.)

(sql-set-product-feature 'postgres
                         :sqli-comint-func #'sql-comint-cockroach)

The usual comint prompt regexp things. This one isn’t that well tested but works on my machine ™ … so far.

(sql-set-product-feature 'postgres
                         :prompt-regexp "^[a-z]+\@[a-zA-Z0-9\.-_]+:[0-9]+/\\([a-z]+\\)?> ")

I don’t really know what this does. The CRDB prompt is not necessarily of a fixed length so it doesn’t really apply. It seems to have no effect, I just cargo culted it from some other DBs. Probably not needed.

(sql-set-product-feature 'postgres
                         :prompt-length 0)

Regexp to match CRDB’s little continuation marker thingy:

(sql-set-product-feature 'postgres
                         :prompt-cont-regexp "^ +-> ")

Set the “end of a SQL statement” character to the semicolon. Some of the other DBs have some pretty fancy settings here, but this seems to mostly work.

(sql-set-product-feature 'postgres
                         :terminator '(";" . ";"))

Command to show all the tables in the current database.

(sql-set-product-feature 'postgres
                         :list-all "SHOW TABLES;")

And finally, this is the comint function we need to work with CRDB:

(defun sql-comint-cockroach (product options)
  "Create comint buffer and connect to CockroachDB."
  (let ((params
         (append
          (if (not (= 0 sql-port))
              (list "--port" (number-to-string sql-port)))
          (if (not (string= "" sql-user))
              (list "--user" sql-user))
          (if (not (string= "" sql-server))
              (list "--host" sql-server))
          options
          (if (not (string= "" sql-database))
              (list sql-database)))))
    (sql-comint product params)))

Advertisements

How to Rock CSV files with SQLite

Hey, have you ever had to work with large quantities of CSV data (read: Excel) in a structured way? Has that experience ever made you want to smash something in a fit of rage? Have you ever wondered why you’re wasting your precious time on this earth reading half-arsed Excel tutorials from 2001 to run a simple query across some data set? If you’re anything like me, the answer is yes. Here’s how to banish that problem from your world forever (at least the CSV-related bits).

In this post, I will describe how to import CSV files into SQlite 3 and do some SQL-y things to them (also known as “rocking” them). There are many other tutorials on how to do this sort of thing; this one is mine. It assumes you have SQLite already installed on your computer.

I’m using the following version:

$ sqlite3 --version
3.7.12 2012-04-03 19:43:07 86b8481be7e76cccc92d14ce762d21bfb69504af

The Data Set

Here’s the CSV data set we’ll be working with – the columns are id, advertiser, line_item, campaign, impressions, and clicks. It’s in a file called tiny-db.csv.

1,  A          , ACME Axe Co.                        , Midwest Lumberjack Competitors           ,        1792 ,     21 
2, A          , ACME Axe Co.                        , Northwestern Logging Equipment Intenders ,        4355 ,     34 
3, A          , Bar-None Ice Cream Cones            , New York Summer Heatwave                 ,      78231 ,   1408 
4, B          , Candles R-US                        , Home Decor Enthusiasts                   ,        2843 ,     65 
5, B          , Doggie Accessories                  , Southwestern Evening Weekends            ,        9486 ,    123 
6, C          , Evergreen Synthetic Xmas Trees      , Sun Belt Homeowners                      ,        2238 ,     46 
7, D          , Frog Hollow Athletic Socks          , Back-to-School                           ,        8198 ,    214 
8, D          , Frog Hollow Athletic Socks          , Practical Holiday  Shoppers              ,         103 ,     12 
9, E          , Best Soap & Candle                  , Clean Hands Stay Healthy                ,        3883 ,     41 
10, E          , Best Soap & Candle                  , Clean Hands Make Good Neighbors          ,        1292 ,    183 
11, E          , Best Soap & Candle                  , Bath Salt Relaxation                     ,         902 ,     81 
12, E          , Best Soap & Candle                  , Bath Salt Relaxation (plus Candles!)     ,        5352 ,    212 
13 , F          , Farmer Snowplows                    , Northeast Winter Storms                  ,      12448 ,    256 
14, F          , Farmer Snowplows                    , Upper Midwest Winter Storms              ,      23984 ,    782 
15, F          , Farmer Snowplows                    , Rocky Mountain Winter Storms             ,       8764 ,    128 
16, G          , Gretchen's Organic Chocolates       , East Coast NPR Podcast Listeners         ,      48996 ,    973 
17, H          , Hap's Go-Kart Track and Petting Zoo , City Folks; Country Weekends             ,        1108 ,     87 
18, H          , Hap's Go-Kart Track and Petting Zoo , Go-Kart Enthusiast Forums (Weekend)      ,        1872 ,    116 

Don’t worry too much about what those terms mean, they mostly have to do with online advertising stuff and aren’t that relevant, except to say that:

  • Impressions are those moments when a person views an ad on a website
  • Clicks are recorded when people click on advertisements
  • I totally made them up (this should be obvious from the names, but still…)

Importing the CSV File

Here’s how to get the data into sqlite: first you have to make a table in the database that matches the columns in your CSV file. Then, set the comma as your separator, and import the data. Note that you may run into issues with fields that have commas inside them such as “Hello, World!”; it’s best to clean them up somehow beforehand.

rloveland:code rloveland$ sqlite3 
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> create table tiny (id integer, advertiser text, line_item text, campaign text, impressions integer, clicks integer);

sqlite> .separator ","

sqlite> .import tiny-db.csv tiny

Querying the Data Set

Now we can run SQL statements on the data.

How many rows had more than 9000 impressions?

sqlite> select * from tiny where impressions > 9000;

3, A          , Bar-None Ice Cream Cones            , New York Summer Heatwave                 ,78231,1408
5, B          , Doggie Accessories                  , Southwestern Evening Weekends            ,9486,123
13, F          , Farmer Snowplows                    , Northeast Winter Storms                  ,12448,256
14, F          , Farmer Snowplows                    , Upper Midwest Winter Storms              ,23984,782
16, G          , Gretchen's Organic Chocolates       , East Coast NPR Podcast Listeners         ,48996,973

How many rows had 10000 impressions or more? Can we order them by campaign?

sqlite> select * from tiny where impressions >= 10000 group by campaign;

16, G          , Gretchen's Organic Chocolates       , East Coast NPR Podcast Listeners         ,48996,973
3, A          , Bar-None Ice Cream Cones            , New York Summer Heatwave                 ,78231,1408
13, F          , Farmer Snowplows                    , Northeast Winter Storms                  ,12448,256
14, F          , Farmer Snowplows                    , Upper Midwest Winter Storms              ,23984,782

Using Full Text Search

Finally, let’s set up full text searching. We have to use something called a `virtual’ table that mirrors our existing table.

sqlite> create virtual table tiny_fts3 using fts4 (id, advertiser, line_item, campaign, impressions, clicks);

sqlite> insert into tiny_fts3(id, advertiser, line_item, campaign, impressions, clicks) select id, advertiser, line_item, campaign, impressions, clicks from tiny;

Let’s run a few text searches using the MATCH keyword to make sure it works:

sqlite> select * from tiny_fts3 where line_item match('Acme');

1,  A          , ACME Axe Co.                        , Midwest Lumberjack Competitors           ,1792,21
2, A          , ACME Axe Co.                        , Northwestern Logging Equipment Intenders ,4355,34

sqlite> select * from tiny_fts3 where line_item match('Candle');

9, E          , Best Soap & Candle                  , Clean Hands Stay Healthy                ,3883,41
10, E          , Best Soap & Candle                  , Clean Hands Make Good Neighbors          ,1292,183
11, E          , Best Soap & Candle                  , Bath Salt Relaxation                     ,902,81
12, E          , Best Soap & Candle                  , Bath Salt Relaxation (plus Candles!)     ,5352,212

Conclusion

That’s pretty much the whole show. Now go forth and become the master of all CSVs you survey!