Clojurians Log v2

For me, the latter is still common but supplementary.

I would say a mix, I gravitate towards composing the honeysql if I can as they are basically saved queries which you can compose, but often when working out a more complex query or working with more advanced db stuff I return to using a GUI

I don't use a DB GUI or CLI at all. I use Clojure for all my DB stuff, either simple SQL and next.jdbc directly or HoneySQL and next.jdbc. Always written into a file and eval'd into a REPL.

@seancorfield what do you do to fill the gap of schema visualization that some GUI tools offer?

I haven't found that I need that. We have over 300 tables in our database so I'm not sure that a visual representation would be very readable? We can DESCRIBE and SHOW INDEX via next.jdbc, and datafy / nav allows me to navigate through the data in Portal from table to table.

For gnarly SQL (say I need to do a bunch of CTEs and some json stuff) I’ll pull up the DB console in IntelliJ. Once I get it right, I’ll Honey-ify it. Ideally, though, I’ve got enough prior art stored around that I can just grab something similar and edit that. My preference is the REPL + HoneySQL queries.

hmmm, i think if you do (honeysql/format q {:dialect :mysql}) it should put backticks around names, im sure theres a specific option for that single behavior in there

(-> {:select [:mulog/timestamp
              :mulog/event-name]
     :from :Log
     :where [:= [[:raw "`mulog/timestamp`"]] "foo-account-id"]
     :limit 2000}
    (sql/format {:inline true :dialect :mysql}))

["SELECT `mulog`.`timestamp`, `mulog`.`event-name` FROM `Log` WHERE (`mulog/timestamp`) = 'foo-account-id' LIMIT 2000"]

Yeah, there's some weird aspects of NRQL that you just aren't going to be able to produce from HoneySQL as it stands right now.

You can use :'mulog/timestamp to produce mulog/timestamp as a column name but that defeats the quoting (by design) and there's no way to generate columns containing / right now.

As for since, you'd have to use register-clause! to add your own formatter. I don't have a good example for you right now because the closest formatter would wrap the expressions in parens which you don't want.

makes sense, thank you for the information

I'm not averse to adding support for NRQL but it's going to be quite a bit of work since there's a lot of New Relic-specific quirks in that syntax.

yeah, seeing that now

this seems to work well enough:

(sql/register-clause! :since (fn [clause x] [(str (name clause) " " x)]) :limit)

:since "2 days ago"

Yeah, I was thinking of :since [2 :days :ago] and formatting it fully but that works with a string 🙂

hahaha, that looks better

will see how far this gets me and report back

The main convenience you'll want is to have a way to override the / and . splitting in column names...

(and you'll probably want it to not convert - to _ in column names as well?)

yeah

thank you

You only need the backtick quotes on names that contain stuff like /, ., and - right? I'm just thinking what this dialect might look like...

The main changes would be in format-entity that this dialect would have to force col-fn to (almost) identity I think and make the parts logic a no-op...

(format {:select [:mulog/timestamp :mulog/event-name]
           :from   :Log
           :where  [:= :mulog/data.account "foo-account-id"]}
          {:dialect :nrql :quoted nil :inline true})
;;=> ["SELECT `mulog/timestamp`, `mulog/event-name` FROM Log WHERE `mulog/data.account` = 'foo-account-id'"]

(I can make the :nrql dialect automatically select :quoted nil :inline true)

@nate Off the top of your head, do you remember if NRQL has ORDER BY and whether it comes before or after SINCE?

I don’t know if I’ve used order by before. I would assume it would be before since.

It's a bit more limited than I thought

OK, I don't have FACET or WITH TIMEZONE yet:

(format {:select [:mulog/timestamp :mulog/event-name]
           :from   :Log
           :where  [:= :mulog/data.account "foo-account-id"]
           :since  [2 :days :ago]}
          {:dialect :nrql :quoted nil :inline true})

["SELECT `mulog/timestamp`, `mulog/event-name` FROM Log WHERE `mulog/data.account` = 'foo-account-id' SINCE 2 DAYS AGO"]

I'll push this to develop and you can try it out via git deps (it'll also push a new SNAPSHOT to Clojars shortly).

Awesome! Thank you. I’ll try this out this evening.

https://github.com/seancorfield/honeysql/issues/510 has the current status of the work. It's pushed to develop and the latest 2.4.9999-SNAPSHOT.

This works perfectly! I tried out since, until, compare-with, and timeseries. TIL about the arg to timeseries to specify the units.

Hmm, is that arg optional? I'll have to dig into some more of our NRQL queries at work and see what syntax we use.

Are you invoking the CLI nr1 query programmatically at this point, or still copy'n'pasting?

I've always just put TIMESERIES without any arg, which I'm guessing defaults to TIMESERIES auto

For my experiments just now, I was copy/pasting into the new relic web site, but for my previous hard-coded queries, I was running newrelic nrql query ... programmatically

(via babashka.process/process)

I guess :timeseries [:auto] works... I could tweak it so :timeseries :auto also works, which would be a bit more succinct but I'm not sure it's worth it?

Hm. Consistency is good. But also, eliding unnecessary brackets is good. Hm.

I lean toward the former.

The shortcut can always be added "later"...

Agreed. Easier to loosen later.

Metric names with spaces in are a bit of a pain:

(sql/format {:select [[[:latest (keyword "Elastic/Search OK Count/value")]]
                        [[:latest (keyword "Elastic/Search Fail Count/value")]]
                        [[:* 100 [:latest :Sent/rate]] "Sent/rate x 100"]]
               :from :Metric
               :where [:and [:= :environment "production"]
                       [:= :process.name "match"]]
               :since [1 :day :ago]
               :timeseries [:auto]}
              {:dialect :nrql})

["SELECT LATEST(`Elastic/Search OK Count/value`), LATEST(`Elastic/Search Fail Count/value`), 100 * LATEST(`Sent/rate`) AS `Sent/rate x 100` FROM Metric WHERE (environment = 'production') AND (`process.name` = 'match') SINCE 1 DAY AGO TIMESERIES auto"]

Could it just be the string instead?

Then it gets single quotes ' instead of backtick.

(our fault for using spaces in metric names, I guess... but...)

New Relic metrics can have any arbitrary name...

Much more flexible than db column names.

It may be a problem that is not worth solving. I'll see whether anyone (else) ends up using this dialect and whether it causes them problems 🙂

I’ll try it out with our metrics too and see how it feels.

Tried out the latest commit for facet, and tried to add it with multiple facets. Looked at the code and I think this is the change needed to make multiple facets work:

❯ git di
diff --git a/src/honey/sql.cljc b/src/honey/sql.cljc
index 63b34fe..e4e6ce5 100644
--- a/src/honey/sql.cljc
+++ b/src/honey/sql.cljc
@@ -1447,7 +1447,7 @@ (def ^:private clause-format
          :returning       #'format-selects
          :with-data       #'format-with-data
          ;; NRQL extensions:
-         :facet           #'format-selector
+         :facet           #'format-selects
          :since           #'format-interval
          :until           #'format-interval
          :compare-with    #'format-interval
diff --git a/src/honey/sql/helpers.cljc b/src/honey/sql/helpers.cljc
index d43f533..844c068 100644
--- a/src/honey/sql/helpers.cljc
+++ b/src/honey/sql/helpers.cljc
@@ -1023,7 +1023,7 @@ (defn within-group
 (defn facet
   "(facet [:metric.name :alias])"
   [& args]
-  (generic-1 :facet args))
+  (generic :facet args))
 
 (defn since
   "(since 2 :days :ago)"

Now :facet [[:mulog/event-name :foo] :container_name] translates to

FACET `mulog/event-name` AS foo, container_name

Ah yes. Less convenient to write a single facet but more flexible. I hadn't thought about multiple facets in a single statement

I'll make that change shortly (not at my desk yet).

(that change has just been pushed -- thank you!)

Thank you!