2.4.3 Analyzing Data from Data Boutique’s AWS S3 in Looker

Updated by Andrea Squatrito

Analyzing Data from Data Boutique’s AWS S3 in Looker

Data Boutique offers datasets stored in AWS S3, and you can bring this data into Looker to explore and visualize it. This guide covers how to set up Looker to access and analyze your Data Boutique data, starting from transferring the data from S3 to Google Cloud Storage (GCS) and then configuring Looker to query and visualize it.

Prerequisite: Before beginning, transfer your data from Data Boutique’s AWS S3 bucket to Google Cloud Storage (GCS). For details, refer to Copying Data from Data Boutique's AWS S3 to Google Cloud Storage.

Step 1: Set Up Your Data in Google Cloud Storage (GCS)

Looker requires data to be accessible through a supported database or data warehouse, and Google BigQuery is a common choice that integrates seamlessly with GCS. By first uploading your Data Boutique data to GCS, you can set it up in BigQuery and configure Looker to connect to it.

  1. Upload Data to GCS:
  2. Create an External Table in BigQuery:
    • In Google BigQuery, create an external table that references the data files stored in GCS. This lets you query data in GCS without loading it fully into BigQuery storage.
    CREATE OR REPLACE EXTERNAL TABLE `your_project.your_dataset.your_table`
    OPTIONS (
      format = 'CSV',
      uris = ['gs://your-gcs-bucket-name/*.csv']
    );

Step 2: Connect Looker to BigQuery

  1. Create a BigQuery Connection in Looker:
    • In the Looker Admin panel, go to Connections and add a new BigQuery connection.
    • Enter your project ID and select the GCS bucket where your Data Boutique data is stored.
    • Configure any additional settings such as authentication, which may require a service account key.
  2. Define the Schema and Fields in LookML:
    • Create a new LookML project in Looker and define your data schema. LookML allows you to model your BigQuery table for flexible querying and visualization.
  3. Create a LookML Model:
    • In the LookML project, create a model file that defines your data source in BigQuery:
      connection: "your_bigquery_connection"
      include: "*.view"
  4. Set Up LookML Views for Your Table:
    • Create a view file in LookML for your BigQuery external table. Define each field, measure, and dimension to make it accessible in Looker.
      view: data_boutique_data {
        sql_table_name: `your_project.your_dataset.your_table` ;;

        dimension: col1 {
          sql: ${TABLE}.col1 ;;
        }

        measure: count {
          type: count
        }
      }
  5. Build Explores in LookML:
    • Define an Explore in LookML that links your view to the Looker frontend, allowing users to run queries and create visualizations.

Step 3: Start Analyzing Data in Looker

Once your LookML model and views are set up, you’re ready to start analyzing the data.

  1. Create and Customize Visualizations:
    • In Looker’s Explore interface, select your LookML model and start building visualizations.
    • Use Looker’s tools to create charts, dashboards, and reports tailored to your specific needs.
  2. Set Up Schedules and Alerts:
    • Automate reporting by setting up schedules for Looker dashboards or specific queries.
    • Configure alerts to notify you of significant changes in data, helping you stay updated with real-time insights.
  3. Share Reports and Dashboards:
    • Share your dashboards or reports with colleagues by providing access to Looker or exporting the reports.

Conclusion

By following these steps, you can easily set up Looker to access, explore, and visualize Data Boutique data transferred from AWS S3 to Google Cloud Storage. This workflow leverages Looker’s powerful visualization and analysis capabilities, helping you gain insights from your data without storing it directly in Looker’s platform.

How did we do?