looker-open-source / looker-explore-assistant Goto Github PK

I followed the BigQuery Deployment instructions and managed to get through the LLM deployment steps, working around the following issues:

1. The instruction to use the notebook to create the stringified examples doesn't include any instructions on how to run the notebook - how do you run it?

2. The step that tells you to:

INSERT INTO explore_assistant_demo_logs.explore_assistant_examples (explore_id,examples)
    VALUES ('model:explore',examples);

references a dataset name that we've not previously created (explore_assistant_demo_logs) and so I created that dataset and then inserted into the explore_assistant_examples table the following values, and repeated this for a table of the same name in the explore_assistant dataset that we had been working with up until this point.

insert into explore_assistant_demo_logs.explore_assistant_examples
  values('model:explore',"""
    input: companies with revenue > 100
    output :fields=companies_dim.company_name,projects_invoiced.total_invoiced_net_amount_gbp&f[projects_invoiced.total_invoiced_net_amount_gbp]>=100000&sorts=projects_invoiced.total_invoiced_net_amount_gbp desc &limit=500
 """)

Then I followed the instructions under "2. Looker Extension Framework Setup".

1. For step 2, you suggest running these steps from Cloud Shell, but every time I try to do this I run out of space, it seems that the files that npm install downloads and tries to unpack eventually exceed the 5GB limit we have when using Cloud Shell.

2. Doing this from my Mac, I think, works? But when you say "You may need to update your Node version or use a Node version manager to change your Node version." it's not clear what version of Node we should install? I went with the default for the Mac but I don't really know if the rest of the steps worked ok or not, this bit of the instruction isn't very clear - this was the output from my attempt to run this install:

markrittman@Marks-iMac extension-bigquery-deployment % npm install
npm WARN EBADENGINE Unsupported engine {
npm WARN EBADENGINE   package: '[email protected]',
npm WARN EBADENGINE   required: { node: '>=14 <17' },
npm WARN EBADENGINE   current: { node: 'v20.11.1', npm: '10.2.4' }
npm WARN EBADENGINE }
npm WARN deprecated @babel/[email protected]: This proposal has been merged to the ECMAScript standard and thus this plugin is no longer maintained. Please use @babel/plugin-transform-class-properties instead.
npm WARN deprecated @babel/[email protected]: This proposal has been merged to the ECMAScript standard and thus this plugin is no longer maintained. Please use @babel/plugin-transform-object-rest-spread instead.
npm WARN deprecated [email protected]: Use String.prototype.trim() instead
npm WARN deprecated [email protected]: this library is no longer supported
npm WARN deprecated @babel/[email protected]: This proposal has been merged to the ECMAScript standard and thus this plugin is no longer maintained. Please use @babel/plugin-transform-object-rest-spread instead.
npm WARN deprecated [email protected]: request-promise-native has been deprecated because it extends the now deprecated request package, see https://github.com/request/request/issues/3142
npm WARN deprecated [email protected]: Please upgrade  to version 7 or higher.  Older versions may use Math.random() in certain circumstances, which is known to be problematic.  See https://v8.dev/blog/math-random for details.
npm WARN deprecated [email protected]: standard 16.0.0 and eslint-config-standard 16.0.0 no longer require the eslint-plugin-standard package. You can remove it from your dependencies with 'npm rm eslint-plugin-standard'. More info here: https://github.com/standard/standard/issues/1316
npm WARN deprecated [email protected]: request has been deprecated, see https://github.com/request/request/issues/3142

added 1183 packages, and audited 1184 packages in 1m

3. For Step 4 where you say "Start the development server IMPORTANT If you are running the extension from a VM or another remote machine, you will need to Port Forward to the machine where you are accessing the Looker Instance from. Here's a boilerplate example for port forwarding the remote port 8080 to the local port 8080: ssh username@host -L 8080:localhost:8080." - I understand the concept of port forwarding but where exactly do you run this command? On my Mac? On a VM if I'd have run these steps there? Somehow on the Looker server? I just ignored these steps in the end.

4. Then for Step 5 you say "IMPORTANT please paste in the deployed Cloud Function URL into the external_api_urls list. This will allowlist it in Looker for fetch requests." - what cloud function? I see you mention creating one in another set of steps that are an alternative deployment approach to the BigQuery deployment, but are you now saying we have to deploy that cloud function anyway?

5. I then went to the Deployment section and managed to put together the manifest file, I think correctly but I'm guessing at this stage:

project_name: "analytics"
application: explore-assistant {
  label: "Explore Assistant"
  # file: "explore-assistant.js"
  file: "bundle.js"
  entitlements: {
    external_api_urls: ["https://localhost:8080","http://localhost:8080"]
    core_api_methods: ["lookml_model_explore","run_inline_query","create_sql_query","run_sql_query"]
    navigation: yes
    use_embeds: yes
    use_iframes: yes
    new_window: yes
    new_window_external_urls: ["https://developers.generativeai.google/*"]
    local_storage: yes
  }
}

Running the Explore Assistant extension brings-up this screen, which looked promising but I noted, didn't reference any of the example queries I inserted into the explore_assistant_examples table earlier:

I then try entering a natural language query e.g. "total invoiced revenue by company" and whilst the explore assistant extension app just hangs, if I then check the Job History tab in the BigQuery Studio web app I can see that the app has sent the query to BigQuery DBML, as shown in the truncated SQL text below:

          DECLARE context STRING;
          SET context = """Youre a developer who would transalate questions to a structured URL query based on the following dictionary - choose only the fileds in the below description
          user_order_facts is an extension of user and should be used when referring to users or customers.Generate only one answer, no more.""";

          SELECT ml_generate_text_llm_result AS generated_content
          FROM ML.GENERATE_TEXT(
              MODEL analytics_ai.explore_assistant_llm,
              (
                  SELECT FORMAT('Context: %s; LookML Metadata: %s; Examples: %s; input: %s, output: ',context,"Dimensions Used to group by information (follow the instructions in tags when using a specific field; if map used include a location or lat long dimension;): name: companies_dim.company_name, type: string, description: , tags: ;name: rfm_model.company_pk, type: string, description: , tags: ;name: companies_dim.company_description, type: string, description: Company Bio, sourced from LinkedIn via Hubspot, tags: ;name: companies_dim.company_industry, type: string, description: , tags: ;name: 
...
contracts_fact.avg_pct_signatures_remaining, type: average, description: , tags: ;name: client_concentration.count, type: count, description: , tags: ",examples.examples, "total invoiced revenue by company") as prompt
                  FROM explore_assistant.explore_assistant_examples as examples
                  WHERE examples.explore_id = "analytics:companies_dim"
              ),
                  STRUCT(
                      0.1 AS temperature,
                      1024 AS max_output_tokens,
                      0.95 AS top_p,
                      40 AS top_k,
                      TRUE AS flatten_json_output
              )
          )

However the Explore Assistant app never returns any results.

In summary:

1. It's never really clear to me whether the npm install part worked and whether I still needed to switch Node version (to what version? 17?)

2. Do we need to deploy the cloud function or not? If so then the steps to do so need to be added to the BigQuery Deployment set of steps

3. The bit about port forwarding and running this on your local machine (or a VM?) really aren't that clear and do we really need them, if the goal is to get this all running independently on your Looker instance?

4. Whilst despite all of the above, the extension app seems to be working at the end and sending requests to my BigQuery instance, the app never renders any results, or has my example queries listed for selection and running (and has a load of boilerplate text that wouldn't apply to a customer deployment i.e. the ecommerce data)

Hope this helps and looking-forward to getting a version running at some point! Cheers

Hi,

The install instructions in the readme file don't work unless, I suspect, the reader has all of the development software already pre-installed that Luka has on his development workstation.

For example, running them on my Mac (Sonoma 14.3 with Intel CPU):

1. git clone [email protected]:LukaFontanilla/looker-explore-assistant.git

gives the error message: `"Cloning into 'looker-explore-assistant'...
[email protected]: Permission denied (publickey).
fatal: Could not read from remote repository.

Please make sure you have the correct access rights
and the repository exists."`

and instead I have to use git clone https://github.com/LukaFontanilla/looker-explore-assistant.git

2. The steps that use Terraform e.g. terraform init, terraform apply etc need Terraform to be first installed, which on my Mac needs me to run:

brew tap hashicorp/tap
brew install hashicorp/tap/terraform

which then errors unless I've run brew install gcc, which then requires me to run xcode-select --install .. etc etc

Could you either (1) update the install instructions to either list-out what needs to be installed to make the development environment sufficient to then run you instructions, correcting the git step so that it works without a private/public key, or (2) is there a way you can pre-build the code, as has been done with the other Gen AI Extension Framework example that's available for Looker that include another take on natural-language querying of Looker explores?

Btw - great work on this example and thanks for making it available ;-)

Mark

right now , explore assistant frontend has capability to interact with vertex ai cloud function or big query backend. In some cases where organisations are having self hoisted looker and non GCP environment, they might need to interact with their custom backend deployed may be on AWS or on Prem etc.

I have the looker assistant up and running - I have not trained the model yet but every query from the assistant is ending with the following error

Java::NetStarschemaClouddbJdbc::BQSQLException: Query execution failed: - Model not found: looker-core-422716.explore_assistant.explore_assistant_refinement_examples at [6:15]

There's a typo in url, right now there's
url: "https://localhost:8000/bundle.js"
should be
url: "https://localhost:8080/bundle.js"

current :

application: explore-assistant {
 label: "Explore Assistant"
 # file: "bundle.js"
 url: "https://localhost:8000/bundle.js"
 entitlements: {
     core_api_methods: ["lookml_model_explore","run_inline_query","create_sql_query","run_sql_query"]
     navigation: yes
     use_embeds: yes
     use_iframes: yes
     new_window: yes
     new_window_external_urls: ["https://developers.generativeai.google/*"]
     local_storage: yes
 }
 }

on "npm install" getting error npm WARN tar TAR_ENTRY_ERROR ENOENT: no such file or directory

Is there a way to add Custom Measures to the explore? Like if I want to show Average Sales by Month, how do I show it on the explore as a visualisation?

Hi, thanks for the work on all of these. I was successfully able to implement your other two extensions after a bit of troubleshooting. Unfortunately, front-end webdev is one of my weaker skill sets and I'm hoping someone can help point me in the right direction.

I have completed backend set-up and working on the front end. When I execute npm run start it appears as if the local server is running but upon navigating to https://localhost:8080/ all I see displayed is
"Cannot GET /"

When I attempt to use the extension I also encounter runtime errors. I have attached relevant screenshots. Thanks.

Which files do I set these infomation?

VERTEX_AI_ENDPOINT=<This is your Deployed Cloud Function Endpoint> LOOKER_MODEL=<This is your Looker model name> LOOKER_EXPLORE=<This is your Looker explore name>

I'm seeing the following runtime error when I try to run the extension locally:

I think it might be an issue with how the examples were loaded to Big Query. Is explore_assistant_examples supposed to have a separate row for each example (i.e. {'input': ..., 'output': ...}) or is it supposed to have a single row with the examples packed into an array (e.g. [{'input': ..., 'output': ...}, {'input': ..., 'output': ...}]).

We are trying to get explore in extension but are facing the issue of unhandled lowloevel error, particularly in LookerEmbedSDk in ExplorEmbed.tsx . The function createExploreWithID is giving us error. Below is screenshot of our error. We do not know why there is issue in connect although we have requires permission

.
Could you please look into it?
Thanks

hi,

trying to clone repo in Windows, getting the following error?

$ git clone [email protected]:looker-open-source/looker-explore-assistant.git
Cloning into 'looker-explore-assistant'...
remote: Enumerating objects: 330, done.
remote: Counting objects: 100% (93/93), done.
remote: Compressing objects: 100% (21/21), done.
Receiving oremote: Total 330 (delta 78), reused 72 (delta 72), pack-reused 237
Receiving objects: 100% (330/330), 645.30 KiB | 1.54 MiB/s, done.
Resolving deltas: 100% (187/187), done.
error: invalid path 'explore-assistant-api/thelook::order_items.jsonl'
fatal: unable to checkout working tree
warning: Clone succeeded, but checkout failed.
You can inspect what was checked out with 'git status'
and retry with 'git restore --source=HEAD :/'

Would y consider renaming the file: "thelook::order_items.jsonl"; the error is likely due to the double colons (::).

Hi
I am trying to create remote connection to gemini-pro model from BQ but i keep getting this error.
Error in query string: Error processing job 'projectid:bqjob_*******************': Unrecognized name: gemini; failed to set 'endpoint' in OPTIONS() at [4:29]

also i see below error when i run the same create model command from notebook even though connection exists.
Exception: Error There's an error occur: {Location: ""; Message: "Not found: Connection explore_assistant_llm"; Reason: "notFound"

Am i missing anything in the backend setup ?

Thanks
Venkata S

Hi Team
I see we have to setup environment variables on the extension setup and we must specify model and explore names. That means we to create one extension per explore? Can we bundle the extension for all explores in the looker environment? TIA for your response.

Thanks
VS

Thanks to the updates in the latest PR I was able to implement the explore assistant but have not been able to unlock the chat functionality. Upon clicking the chat button I see a spinning cirlce indicating loading but nothing ever refreshes and I am unable to find anything in the console logs to support this. Thanks for any help you can provide.

See screenshot:

Can you just point out what is the final prompt or content is going inside LLM in main branch ? because I see content going inside LLM is only user query(NLP) in main branch and that will not give us good inference.

In step 6 of the BigQuery Deployment set of instructions, the step tells you to run the following DDL:

CREATE OR REPLACE MODEL
    `PROJECT_ID.DATASET_ID.explore_assistant_llm`
    REMOTE WITH CONNECTION `PROJECT_ID.REGION.explore_assistant`
    OPTIONS (ENDPOINT = 'gemini-pro');

but the connection name should be PROJECT_ID.REGION.explore_assistant_llm - I think the connection name and dataset name you define in the next step got mixed-up here?

Hello,

I have followed the guide for the BigQuery deployment, including creating the BQ connection, LLM model, and dataset. I have also built the Looker extension using npm run build and uploaded the bundle.js file to my Looker instance.

When I run the extension in Looker it hangs. When I look in the developer console as I first load the extension I see a 404 error from the Looker SDK. The URL is trying to connect to is: https://.looker.app/api/internal/core/4.0/lookml_models/%3CThis%20is%20your%20Looker%20Model%20Name%3E/explores/%3CThis%20is%20your%20Looker%20Explore%20Name%3E?fields=fields

It appears that the LOOKER_EXPLORE and LOOKER_MODEL variables are not set. I set them locally when I ran the extension in my local VM, but it appears that when I package the application for upload to run in Looker itself that the variables are not set.

Do I have to configure the LOOKER_MODEL, LOOKER_EXPLORE and BQML_MODEL_ID within Looker?

Thanks!

I've follow through the steps, except for step 4 on the npm run start as I'm running from cloudshell

Straight jump to step 5 npm run build, had a small error on "clean script not defined", edited the package.json and add clean:"", and successfully built. Copied to looker project and all went fine, the extension UI showed up, but freeze once clicked Run Prompt. Checked bigquery job history and the job was there.

Checked on F12 Dev tool here's the error

Change prompt and it'll appear the same error. Uncaught (in promise) TypeError: Cannot read properties of undefined (reading 'generated_content')
at bundle.js:765:8408

Not sure how to fix this, guidance would be greatly appreciated.

Thanks!