I was also on the verge of trying to use a Docker containerized on-prem Datomic. If I understand correctly, that's not an officially recommended way to run a Datomic system. Can you share some setup instructions for it please?

one possibility is get-else with a sentinel value (not nil)

if the join is just for selecting, consider using pull instead

in which case the “nil” will be just a missing map entry

(d/q '[:find (pull ?e [*]) (pull ?e1 [*]) (pull ?e2 [:bank/name])
       :where
       [?e  :customer/id ?id]
       [?e1 :address/id ?id]
       [(get-else $ ?e2 :bank/id "No Value") ?id]
       [(get-else $ ?e2 :bank/name "No Value") ?name]]
     @conn)

That is what I am trying to do @U09R86PA4

You mean you want the map result of pull to say “No Value”?

That’s just the default value?

what you put here doesn’t make sense

I don’t know what your desired output is

Basically :customer/id and :address/id returns 300,000 results. When I join on bank it returns 10,000

what is ?e2 unifying against?

I effectively want to have the most possible results with the others kind of enriched with extra data when it is found

can [?e :customer/id] be missing?

No

Can [?e :customer/id "No Value"] happen?

no

or [?e1 :address/id "No Value"]?

Like I want it to start with :customer and effectively merge each result onto it with default values if they are not present.

Like if I joined a table on a column

the only thing unifying these clauses is ?id so I don’t see what the join is

is the value of :bank/id and :customer/id supposed to unify?

It only returns me results that have a :bank/id so I lose all the previous finds.

is there any ref attribute you can use to walk between a customer, address, and bank?

Yes I am unifying those and then wanting to add some extra data if it is there

I don’t quite understand refs yet so no.

ok, so you’re joining by some concrete value “id” (I guess a string?) which happens to be common to :customer/id :bank/id, and :address/id?

you can do this but it’s not the natural way of modeling relationships in datomic

Yes. But there is very little bank data.

What is the natural way of doing things in datomic? Using the refs etc?

using refs

(I would normally assume a :customer/id was scoped to customers)

so :customer/address -> some entity with address attributes on it

:customer/bank -> some attribute with bank entities on it

at this point you have a single pull expression, because it can see all the joins

you need two steps:

This is kind of a dirty data CSV import. So I haven’t got much luxury in the naming of things. Was just assessing if datalog will help me with pre-processing. But it’s harder than I thought

[?c :customer/id ?id][?a :address/id ?id][?b :bank/id ?id] for those with banks

[?c :customer/id ?id][?a :address/id ?id](not [_ :bank/id ?id]) for those without

get-else isn’t good here because it can’t make use of a value index for :bank/id

So you would split it into 2 queries?

I would

You could unify to one query using or and a sentinel for the missing bank case, but you can’t pull off that sentinel

Thanks. I was trying to avoid that but I guess it requires stronger schema for that.

up to you if it’s worth augmenting your data

it’s easy to find the common values via query and build the tx you need to make the refs

[?c :customer/id ?id][?a :address/id ?id] -> [:db/add ?c :customer/address ?a] for example

The problem I have is that this is a CSV ETL job. Daily drops with millions of rows and 10's of columns. So doing these checks doesn’t seem that efficient.

Though I could be wrong….

The CSV’s is pretty much always the same… with just a bit of new data.

I hate to say it, but sql might be a better fit, depending on what you do

I’m starting to believe it.

if you aren’t becoming the source of truth for what you ingest, and that stuff is stable-shaped already and you just want to make some joins, sql may be better

datomic shines with graph-shaped data you’re growing with (i.e. history of changes is important) and a primary datastore you add to incrementally with a live application

it doesn’t do giant bulk imports well, and it can join by value but you miss out on a lot of graph-shaped niceties

meanwhile some sql engines can query csv directly, import csv with magical fast bulk importers, and are already used to joining by value

e.g. have you considered redshift or athena (for cloud things at huge scale)? I think they both work by sticking table-shaped files (e.g. csv) into s3 and then “just working”

It’s a shame. Because I was using Meander to transform the CSV’s before entering them into the DB. Then I was using the DB for the joins and pulling it all out. The semantics are pretty much the same because they both use logic programming.

We haven’t got huge scale

can you use meander to make the refs ahead of time?

You mean keeping them all to the same ns of the keyword?

I mean, is there something from the unit of work you can use to mint a unique upserting attribute

I don’t think so. It’s all good 🙂

e.g. {:customer/bank [:bank/unique-id customer-id]}

well it wouldn’t be like that exactly

[{:db/id "bank-123" :bank/unique-id "value-derived-from-customer}{:db/id "customer-123" :customer/bank "bank-123, ,, :other/customer "stuff",,,}]

that said, I think whether you use datomic or not should be driven entirely by what you plan to do after you ingest these CSVs

iirc there’s something that can do pulls against postgres

https://walkable.gitlab.io/ ?

@U1C36HC6N the convo.

@U09R86PA4 yea I find it interesting though I also think I need to know SQL and tables a bit more before working with such abstractions.

I downloaded Datomic, added the licence key in dev-transactor-template.properties and ran this script

bin/transactor datomic_properties/dev-transactor-template.properties

is protocol=dev in that file?

in the dev-transactor-template.properties file @U09R86PA4?

yes

@U09R86PA4 So this is what I have so far

protocol=dev
host=0.0.0.0
port=4334

license-key=<MY_KEY>

memory-index-threshold=32m
memory-index-max=256m
object-cache-max=128m

Now I am getting Terminating process - License not valid for this release of Datomic

so you miscopied the key, or the key isn’t valid for the version you’re using

you can check this at your http://my.datomic.com site

probably also in the email that sent you the license key

Okay rookie mistake, my key was expired :face_palm:

licenses are perpetual, so you can use an older version (released before it expired)

you just won’t get updates

@U09R86PA4 thank you so much. Local Transactor is running now

Dev databases are embedded h2 databases, served by the same process as the transactor

As a consequence, the data you handle with Datomic leaves in files on the disks of the transactor machine. From an operations perspective it's not a great architecture to couple these 2 concerns. Your transactor process doesn't need any persisted state, you could run it (or them) on ephemeral machines.

for one of my company internal app, we use datomic dev with h2 database on a dedicated server. works fine til now.

Good points. I had some unexpected time to play last night and already had postgres installed. For what I'm doing, my time to transact a fair amount of data went from 360s with dev to 40s with postgres. Hardly scientific testing, but indicative still.