Skip to main content

HG Data Query (SQL)

Run a structured read-only SQL SELECT query over the HG Insights data warehouse tables; returns rows, column names, a row count, and credits consumed. Use this when you need a custom aggregation, multi-table join, time-series rollup, competitive-displacement (category IN / product NOT IN), or raw exploration that no purpose-built HG tool can express.

ALWAYS call hg_catalog FIRST for exact table/column names — do NOT invent columns. Queries must be SELECT-only (no INSERT/UPDATE/DELETE/DROP); SELECT * is not allowed; violations are rejected upstream.

Do NOT use this when a purpose-built tool already answers the question — they are cheaper, simpler, and need no SQL: company_technographic (a company's tech stack), search_companies (find/count companies by vendor/product/category/geo/size, with built-in groupBy and technologyIds), company_spend, company_install_time_series, get_vendor_information (a vendor's product IDs). Prefer those first.

COST: billed at ~1 credit per row returned (exact amount reported as credits_consumed). Prefer COUNT/aggregate queries and a tight max_rows — thousands of rows cost thousands of credits. A 0-row match returns an empty columns array.

Common columns (confirm via hg_catalog): company_locations has name, country_name, employees_min, employees_max, company_id, url_id; install_global has product_id, product_name, vendor_name, category_leaf_name, url_id (NO category_id). Join the two with USING (url_id).

For TAM: get_vendor_information returns a vendor's product IDs, then join install_global to company_locations for installed-base/displacement counts; derive categories from install_global (never guess a category_leaf_name value). See this tool's examples for full SQL.

Parameters​

NameTypeRequiredDefaultDescription
querystring✅ Yes-A single read-only SQL statement to run against the HG warehouse. Must start with SELECT or WITH; SELECT * and any write/DDL (INSERT/UPDATE/DELETE/DROP) are rejected. Use exact table/column names from hg_catalog and reference at least one allowed warehouse table.
max_rowsinteger❌ No1000Row cap for the result set (default 1000, max 10000). Billed at ~1 credit per row returned, so set this as low as the task allows — prefer COUNT/aggregate queries over pulling raw rows to control cost.

Required Integrations​

  • hginsights_db_query

Use Cases​

  • Run a custom aggregation or cross-table join no purpose-built HG tool can express — call hg_catalog first for table/column names
  • Compute TAM: count distinct companies running a product within a geo/size band (COUNT(DISTINCT ...) over install_global joined to company_locations)
  • Competitive displacement: companies in a category that do NOT run a given vendor (category IN <subquery> AND product_id NOT IN <ids>)
  • Install/spend time-series or trend rollups across warehouse tables not exposed by a specific tool
  • Ad hoc warehouse exploration once you know the exact tables and columns from hg_catalog

Example Usage​

Count mid-market US/Canada companies running Splunk (product IDs from get_vendor_information)

{
  "tool": "hg_data_query",
  "arguments": {
    "query": "SELECT COUNT(DISTINCT cl.company_id) FROM install_global ig JOIN company_locations cl USING (url_id) WHERE ig.product_id IN (1234, 5678) AND cl.country_name IN ('United States', 'Canada') AND cl.employees_min >= 100 AND cl.employees_max <= 1000"
  }
}

Competitive displacement: SIEM installs that are not Splunk

{
  "tool": "hg_data_query",
  "arguments": {
    "query": "SELECT DISTINCT cl.company_id, cl.name FROM install_global ig JOIN company_locations cl USING (url_id) WHERE ig.category_leaf_name IN (SELECT DISTINCT category_leaf_name FROM install_global WHERE product_id IN (1234, 5678)) AND ig.product_id NOT IN (1234, 5678) LIMIT 500",
    "max_rows": 500
  }
}

hg_catalog, search_companies, company_technographic, get_vendor_information, company_install_time_series