Multiple connections in one model

[lisacabisa]

Hi,
I’m new to Looker and I’m trying to figure out how to have a model that uses more than one connection - as is probably the case with many people, my data does not live neatly in one database.
So, I created a model with two connections. I am getting a “relation does not exist error” so I added sql_table_name to my views for each table with the full name ...
I can’t imagine that the data other people use lives neatly in one database… How can I use multiple connections in on model?
Thank you for your help!

[lloydtabb]

HI @lisacabisa

In Looker models are tied to a particular connection, but projects can have multiple models inside them (sharing view files).

If you add a new model in the models section, you can have different connection then include the shared files.

For example, the IMDb model runs against, Redshift, BigQuery and Spark connections. Each connection is a different model but all in the same project with shared view and explore definitions.

The shared explores go into another file besides the model file (or in this case we placed them in ‘base’ file, by checking in directly into the git repository, but you could put them in a view too).

The shared file is below. Notice it has no connection definitions.

https://github.com/looker/imdb/blob/master/imdb.base.lookml

Each of the models then declares its connections and includes. In this particular model, we extend some views to support the particular flavors of the database connection.

imdb.model.lookml

Old LookML

# Redshift implementation of IMDB

- connection: imdb

- include: "*.view.lookml"       # include all the views
- include: "*.dashboard.lookml"  # include all the dashboards

- include: imdb.base.lookml

- case_sensitive: false

- explore: title
  extends: title_base
  # Redshift doesn't support lists, remove the fields.
  fields: [ALL_FIELDS*, -movie_genre.genre_list, -movie_genre2.genre_list]

New LookML

# Redshift implementation of IMDB

connection: "imdb"

# include all the views
include: "*.view.lkml"

# include all the dashboards
include: "*.dashboard.lkml"

include: "imdb.base.lkml"

case_sensitive: no

explore: title {
  extends: [title_base]
  # Redshift doesn't support lists, remove the fields.
  fields: [ALL_FIELDS*, -movie_genre.genre_list, -movie_genre2.genre_list]
}

imdb_bigquery.model.lookml

Old LookML

- connection: bigquery_publicdata

- include: "*.view.lookml"       # include all the views
- include: "*.dashboard.lookml"  # include all the dashboards

- include: imdb.base.lookml

- case_sensitive: false

- view: title_table_name
  sql_table_name: imdb.title

- explore: title
  extends: title_base
  hidden: true
  joins:
    - join: cast_info
      type: left_outer_each
    - join: char_name
      type: left_outer_each
    - join: name
      type: left_outer_each
    - join: cast_title_facts
      type: left_outer_each
    - join: cast_top_genre
      type: left_outer_each
    - join: cast_info2
      type: left_outer_each
    - join: char_name2
      type: left_outer_each
    - join: name2
      type: left_outer_each
    - join: tv_series
      type: left_outer_each
    - join: movie_genre
      type: left_outer_each

New LookML

connection: "bigquery_publicdata"

# include all the views
include: "*.view.lkml"

# include all the dashboards
include: "*.dashboard.lkml"

include: "imdb.base.lkml"

case_sensitive: no

view: title_table_name {
  sql_table_name: imdb.title ;;
}

explore: title {
  extends: [title_base]
  hidden: yes

  join: cast_info {
    type: left_outer_each
  }

  join: char_name {
    type: left_outer_each
  }

  join: name {
    type: left_outer_each
  }

  join: cast_title_facts {
    type: left_outer_each
  }

  join: cast_top_genre {
    type: left_outer_each
  }

  join: cast_info2 {
    type: left_outer_each
  }

  join: char_name2 {
    type: left_outer_each
  }

  join: name2 {
    type: left_outer_each
  }

  join: tv_series {
    type: left_outer_each
  }

  join: movie_genre {
    type: left_outer_each
  }
}

You can see the entire repo here: https://github.com/looker/imdb

Hopefully, this is what you are asking.

[lisacabisa]

Thank you @lloydtabb ! I think this will help me with what I’m trying to do. Just need to get in there and muck around a bit to figure out how to apply it to my situation. Cheers!

[rschoenbeck]

Just a note here that we’ve gotten errors of the form “field named X could not be found” in all joined views using this sort of structure after we transitioned to New LookML in Looker 4.0. Opened a support request.

[tig]

Rob, in new LookML you need to add an include parameters in more places.
Here’s what we’ve added to the doc:

The include parameter is used in view files in two cases, and otherwise is not required or recommended.

First, if a view references another view file to complete an extension, you should use include. For example:

include: "basic_order_fields.view"
view: marketing_order_fields {
  extends: [basic_order_fields]  # The file that contains the basic_order_fields
}                                # view should be included

Second, if a view references fields from another view file, you should use include. For example:

include: "inventory.view"
view: order {
  dimension: profit {
    type: number
    sql: ${sale_price} - ${inventory.cost}  # The file that contains the inventory
  }                                         # view should be included
}

[tig]

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.

[rschoenbeck]

Thanks; still getting errors with Looker being unable to find field sets, but that at least chops the number of validation errors down to about 77 or so.

[psilospore]

Not sure if this is a problem with just the new LookML stuff but if I try to create a base model with no connection then when I attempt to validate I get an error “Model does not have a connection declared. A connection is required”

[leticia_esparza]

Hey @psilospore,

I hope all is well! When we create a model, we want to be sure that we are defining the database connection that the model is referencing. We can do this by leveraging our connection parameter with this syntax in the model file:

connection: "connection_name"

When we do not have the connection defined, this is a case when the error you are seeing on your end surfaces. If we don’t have this, then the model does not know what database connection it should retrieve data from.

Let’s trying adding the connection in that model file by using the above pattern and replacing connection_name with the name of your database connection. Also, for reference, here is a resource with more information about the connection parameter.

Let me know if I can clarify anything about this on my end!

Cheers,

Leticia

[Aslihan_Uysal]

Hi @lloydtabb !

I have a question about this example, does this example use mentioned mean that we can use views on different connections irrespective of model’s connection ? In your example there is two connection, but both of them includes title view. What database does title view read from? Redshift vs bigQuery? I hope, I explained well.
Best,

[diego_campos1]

Hi @Aslihan_Uysal,

As discussed by chat, we can have a view defined and these views included in several models. The only thing to be aware of is that the view in the SQL you are using in the sql parameter for the fields must work across dialects to ensure the queries will run on both models.

Now regarding What database does title view read from? Redshift vs bigQuery?:

Title view is a LookML representation from a table you have, on its own it will not pull anything from anywhere. You need to define the sql_table_name (using proper schema_name.table_name) so this value needs to be shared across the connections (or use liquid to change this dynamically). Once the view is defined, the model you have will use an include statement, this pulls the view into the model, still no data can be accessed until you define an explore that uses/joins in this view. At this point go to explore and query data via model 1 to get the bq data, then repeat steps in your redshift model and go to explore based on that model to get redshift data.

Hope that clarifies this a little bit.