Auto-complete SQL editor

Get Help
1

Hello, I created an EC2 instance in AWS and run metabase docker in it. I also added a connection to a data base, but when I create a query in the SQL query editor, the auto-complete does not work at all, even if I wait for some time, I have suggestions that are not related to my database.
Is there an explanation to this ? And can I setup the auto-complete to give me the rights completions please ?
This is my version of metabase

{
  "browser-info": {
    "language": "en-GB",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:109.0) Gecko/20100101 Firefox/109.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.18+10",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.18",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.18+10",
    "os.name": "Linux",
    "os.version": "5.15.0-1028-aws",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "redshift"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-01-27",
      "tag": "v0.45.2.1",
      "branch": "release-x.45.2.x",
      "hash": "1a59de7"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

Thanks in advance

2

My first suggestion is that you move away from the h2 application database (H2). Try that and then let us know if you start seeing the suggestions

3

Hello, we are working on the Redshift database. The H2 is the default database so we're not using it. The issue is when querying the redshift databse, the schemas don't appear, as for the tables.

4

You’re using the H2 database as your application database (the database where Metabase persists all its entities). If you don’t move away from that you risk losing all your work: Configuring the Metabase application database

5

I changed it and I run the docker image with this option for the autocomplete: -e "MB_NATIVE_QUERY_AUTOCOMPLETE_MATCH_STYLE=prefix"

This is my new configuation, but still no good autocomplete and slowness in execution

{
  "browser-info": {
    "language": "en-GB",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:109.0) Gecko/20100101 Firefox/110.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.18+10",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.18",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.18+10",
    "os.name": "Linux",
    "os.version": "5.15.0-1030-aws",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "redshift",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "13.6"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.0"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-01-27",
      "tag": "v0.45.2.1",
      "branch": "release-x.45.2.x",
      "hash": "1a59de7"
    },
    "settings": {
      "report-timezone": null
    }
  }
}
6

how many tables do you have? how many fields? the autocomplete works by firing a query to the app db and coming up with 50 results. When you say "no good" and "slowness", can you measure those?

7

We have different tables on a single schema, and we have lots of schemas. Same thing for the fields, we're using it for Big Data purpose.
When I say no good autocomplete, it means that there is no suggestions at all about our schemas neither tables in a schema. And it's slow when retrieving the results, I don't know if it's related to the way Metabase works.

8

What's the sizing of your PostgreSQL app db? do you see the app db cpu or iops peaking when using the autocomplete feature?

9

We're using a large one in AWS (db.t3.large). When using the autocomplete feature it seems like the iops is peaking a little bit but not that much (see image, but do not consider the first peek).

image
image1592×1168 69.2 KB

I can also see in the logs that the API calls for an autocomplete but unfortunately there are no matching results.

image
image1920×1114 451 KB

10

Ok so if you see the logs, Metabase is responding the autocomplete api in milliseconds, so there’s something going on between your browser and Metabase as you should see the results right after you type

11

Hello Luiggi, thanks for your response. Do you have ideas of what can be the cause for this latency ?

And also, with the option "MB_NATIVE_QUERY_AUTOCOMPLETE_MATCH_STYLE=prefix" we still have results as they were searched by substring. Is there something else to setup to improve the autocomplete please ?

12

You need to use the browser developer tools and check the network tab to see why the api calls take more that what the api says (milliseconds), and then you need to investigate where’s the issue on the network that’s causing delays

13

Hello, I checked the network traffic, and there are only GET requests that change everytime I add a letter into my schema or table. It removes also the schema when I write the table. There is nothing else that comes into the network

image
image840×490 25.8 KB

14

What’s the response time of these calls?

15

It's between 128 and 1067ms

image
image2242×494 120 KB

16

That latency is fine, if you have massive amounts of tables and schemas a good option would be to add more indexes to the metabase_table and metabase_fields tables in the db to accelerate a bit those searches, but seems you won’t be able to accelerate it even more

Another option could be to completely override the search endpoint with an external endpoint that looks for fields and tables in a search engine like elasticsearch

17

Hello,

Thanks a lot for your help.

Best,
Khalida.

18

hey! how did everything go? were you able to get this working?

19

Hello :slight_smile:

At first we just tested Metabase with few users. We decided to go with this tool but we're still thinking on how to reimplement the autocomplete and the infrastructure when we'll have ~300 users. It might take some time. If you have any other advice I'll take it though !

Thanks

20

It’s the first time I see such a performance issue, but if you need 300 simultaneous users making sql questions you might want to do some performance tweaks in your app db and try to do some caching