Macro to load json from file

Help
1

The fromjson macro expects the input to be a json string.

Are there any macros to load json from a file?
If there are none, can you help me with pointers on how to write custom macros that do file io?

Thanks.

1 Like
2

@venkatm dbt is designed to work with data that’s already in your database, and doesn’t have the ability to do file IO outside of what your warehouse exposes. What sort of JSON files are you trying to work with?

3

Thanks for getting back to me. I was trying to see if I could dynamically construct the SQL query using attributes read from a json file at runtime, without having to modify the dbt model file.

4

Depending on the number of attributes you need to manage, you might be able to use vars and the fact that you can pass those into a dbt invocation: Project variables | dbt Developer Hub

Can you post an example of the sort of query you want to generate, and which parts of it would vary based on the attributes? It’s hard to be much help in a hypothetical scenario

5

@joellabes: In our case, we want to store large configurations as code within our dbt project. The configs contain metadata about our project, our database, and much more, which come in very handy to dynamically generate stuff in models, tests, macros, etc.

We currently store those configs in a macro (see related thread here), but it would be very nice to be able to store them in a YAML file instead (it would at least provide better IDE support to edit the file).

What would be even better is if dbt provided a way to store custom configs directly within the project itself, perhaps as a custom_conf key in dbt_project.yml. But currently that returns an error:

Runtime Error
  at path []: Additional properties are not allowed ('custom_conf' was unexpected)
6

Would Defining vars, folder-level configs outside dbt_project.yml · Issue #2955 · dbt-labs/dbt-core · GitHub help?

7

Actually, we have now moved to storing these configs in a project variable inside of dbt_project.yml! I think that’s the way to go! :+1:

Not sure why we didn’t do that in the first place. :sweat_smile: :man_shrugging: I guess it felt weird since we usually thought of vars as command line key:value things, not large key:dictionary things.

Like this:

vars:
  configs:
    key: value
    key2: value2
    key3:
      a: 1
      b: 2
      ...

It does make the file much larger, but that’s just a matter of using an IDE which supports code folding. :slight_smile:

And we can use YAML anchors, which is awesome! :ok_hand:

1 Like
8

@joellabes: That being said, do you know if there’s some kind of maximum size for dbt variables? I couldn’t find anything in the documentation.

I tried creating a really large dummy var (a dict with over 10K keys), and it worked. :man_shrugging:

9

I expect you’ll hit comprehensibility limits before you hit any size limit!

1 Like