:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
import { useActions, useMountedLogic, useValues } from 'kea'
import { useEffect, useMemo, useRef, useState } from 'react'
import { useNotebookJupyterCommands } from 'lib/components/MarkdownNotebook/jupyterMode'
import { LemonButton } from 'lib/lemon-ui/LemonButton'
import { LemonTabs } from 'lib/lemon-ui/LemonTabs'
import { OutputTab } from 'scenes/data-warehouse/editor/outputPaneLogic'
import { createPostHogWidgetNode } from 'scenes/notebooks/Nodes/NodeWrapper'
import type { NotebookNodeRunTerminalStatus } from 'scenes/notebooks/Notebook/notebookNodeStalenessLogic'
import { applyVisualizationType, columnsFromResponse } from '~/queries/nodes/DataVisualization/dataVisualizationLogic'
import { Query } from '~/queries/Query/Query'
import { DataVisualizationNode, HogQLQueryResponse, NodeKind } from '~/queries/schema/schema-general'
import { ChartDisplayType } from '~/types'
import { notebookCodeCellLogic } from 'products/notebooks/frontend/notebookCodeCellLogic'
import { notebookJupyterLogic } from '../Notebook/notebookJupyterLogic'
import { NotebookNodeAttributeProperties, NotebookNodeProps, NotebookNodeType } from '../types'
import { NotebookCellOutputHeader } from './components/NotebookCellOutputHeader'
import { NotebookCellOutputNameFooter } from './components/NotebookCellOutputNameFooter'
import { notebookDataframeHintLogic } from './components/notebookDataframeHintLogic'
import { NotebookDataframeHintPopover } from './components/NotebookDataframeHintPopover'
import { NotebookDataframeTable } from './components/NotebookDataframeTable'
import { NotebookJupyterCellOutput } from './components/NotebookJupyterCellOutput'
import { getCellLabel } from './components/NotebookNodeTitle'
import { NotebookRunDownstreamBanner } from './components/NotebookRunDownstreamBanner'
import { NotebookCodeSQLEditorSettings } from './components/NotebookSQLEditor'
import { NotebookStaleCellBanner } from './components/NotebookStaleCellBanner'
import { notebookNodeLogic } from './notebookNodeLogic'
import { initialSizedRunId, outputHeightForShape } from './notebookNodeOutputHeight'
import { SQL_V2_DEFAULT_PAGE_SIZE } from './notebookNodeSQLV2Logic'
import { NotebookDataframeResult } from './pythonExecution'
export type NotebookNodeSQLV2Media = { mime_type: string; data: string }
export type NotebookNodeSQLV2Result = {
columns: string[]
types?: [string, string][]
row_count: number
first_page?: (string | number | null)[][]
previewOnly?: boolean
has_more?: boolean
// Python node output: captured streams and rich media (e.g. matplotlib PNGs).
stdout?: string
stderr?: string
media?: NotebookNodeSQLV2Media[]
// A Python node's last expression as text, for a value that is not a dataframe.
result_text?: string
}
export type NotebookNodeSQLV2Attributes = {
code: string
// Dataframe name other SQLV2 nodes can reference (inlined as a CTE when they join it).
returnVariable: string
// Where the cell runs: a direct-query data source id, or null/absent for PostHog's ClickHouse.
connectionId?: string | null
// Send the code to that connection verbatim instead of compiling it from HogQL.
sendRawQuery?: boolean | null
runId?: string | null
result?: NotebookNodeSQLV2Result | null
// How the run that produced `result` ended. An interrupt persists a partial result just
// like a completed run does, so the result alone can't tell the two apart on a reload.
runStatus?: NotebookNodeRunTerminalStatus | null
outputTab?: OutputTab | null
vizQuery?: DataVisualizationNode | null
}
// Matches the SQL editor output pane's default so charts land at v1-node size.
const VIZ_MIN_HEIGHT = 350
// The dataframe name is referenced as a bare SQL table name and becomes a Python variable
// when a python cell reads the frame, so it must be a plain identifier. Empty is fine —
// the cell is then display-only.
const VALID_RETURN_VARIABLE = /^[A-Za-z_][A-Za-z0-9_]*$/
export const returnVariableValidationError = (returnVariable: string): string | null => {
if (!returnVariable || VALID_RETURN_VARIABLE.test(returnVariable)) {
return null
}
const suggestion = returnVariable.replace(/[^A-Za-z0-9_]/g, '_').replace(/^(?=\d)/, '_')
const hint = VALID_RETURN_VARIABLE.test(suggestion) ? ` Try ${suggestion}.` : ''
// Call out only the rule that was actually broken so a name like `people-df` isn't told it
// "can't start with a number" when the real problem is the hyphen.
const startsWithDigit = /^\d/.test(returnVariable)
const hasInvalidChars = /[^A-Za-z0-9_]/.test(returnVariable)
const reason =
startsWithDigit && hasInvalidChars
? "Use letters, numbers, and underscores, and don't start with a number."
: startsWithDigit
? "The name can't start with a number."
: 'Use letters, numbers, and underscores.'
return `${reason}${hint}`
}
const toDataframeResult = (result: NotebookNodeSQLV2Result): NotebookDataframeResult => {
const columns = result.columns ?? []
const firstPage = result.first_page ?? []
return {
columns,
rows: firstPage.map((row) => Object.fromEntries(columns.map((column, index) => [column, row[index] ?? null]))),
// Page over what we actually have; the envelope only carries the first page.
rowCount: firstPage.length,
}
}
// Results from before the envelope carried types (or a kernel that omitted them):
// approximate from the first non-null cell so numeric axes still work in charts.
const inferTypes = (result: NotebookNodeSQLV2Result): [string, string][] =>
(result.columns ?? []).map((column, index) => {
const sample = (result.first_page ?? []).map((row) => row[index]).find((cell) => cell !== null)
return [column, typeof sample === 'number' ? 'Float64' : 'String']
})
// Kernel results can contain pandas dtypes. Charts need ClickHouse type names to select numeric axes.
const PANDAS_DTYPE_NAMES: Record<string, string> = {
bool: 'Bool',
boolean: 'Bool',
category: 'String',
object: 'String',
str: 'String',
}
const toClickhouseTypeName = (type: string): string => {
const dtype = type.toLowerCase()
if (PANDAS_DTYPE_NAMES[dtype]) {
return PANDAS_DTYPE_NAMES[dtype]
}
if (dtype.startsWith('datetime64')) {
return 'DateTime'
}
if (/^u?int(8|16|32|64)$/.test(dtype)) {
return 'Int64'
}
if (/^float(16|32|64)$/.test(dtype)) {
return 'Float64'
}
return type
}
export const toCachedResults = (result: NotebookNodeSQLV2Result): HogQLQueryResponse => ({
results: result.first_page ?? [],
columns: result.columns ?? [],
types: result.types?.length
? result.types.map(([column, type]): [string, string] => [column, toClickhouseTypeName(type)])
: inferTypes(result),
})
const Component = ({
attributes,
updateAttributes,
}: NotebookNodeProps<NotebookNodeSQLV2Attributes>): JSX.Element | null => {
const nodeLogic = useMountedLogic(notebookNodeLogic)
const { nodeId, notebookLogic, expanded, sqlV2ReturnVariable, sqlV2ReturnVariableUsage, isEditable } =
useValues(nodeLogic)
const { navigateToNode } = useActions(nodeLogic)
const notebookShortId = notebookLogic.props.shortId
const dataLogic = notebookCodeCellLogic(nodeId, notebookLogic, attributes, updateAttributes)
const {
isRunning,
runError,
isRestoringResult,
resultRestoreUnavailable,
page,
pageSize,
pageResult,
pageLoading,
operationBlockReason,
isStale,
staleReason,
isChainRunning,
staleDownstreamCount,
pendingKernelStart,
result: runResult,
} = useValues(dataLogic)
const { setPage, setPageSize, runStaleChain } = useActions(dataLogic)
const isJupyterMode = !!useNotebookJupyterCommands()
const { executionCounts } = useValues(notebookJupyterLogic({ shortId: notebookShortId }))
const usageLabel = (nodeIndex: number | undefined, title: string): string =>
title.trim() || getCellLabel(nodeIndex) || 'SQL'
const result = runResult ?? attributes.result ?? null
const returnVariable = sqlV2ReturnVariable
const returnVariableError = returnVariableValidationError(returnVariable)
// A callback ref, not useRef: the hint anchors to this input, and a ref assignment alone
// wouldn't re-render the Popover with the element it needs to position against.
const [returnVariableInput, setReturnVariableInput] = useState<HTMLInputElement | null>(null)
const { reportRunFinished } = useActions(notebookDataframeHintLogic({ shortId: notebookShortId }))
const runStatus = attributes.runStatus ?? null
const previousRunStatusRef = useRef(runStatus)
useEffect(() => {
const previousRunStatus = previousRunStatusRef.current
previousRunStatusRef.current = runStatus
// Only a run that finishes while the cell is open. Reopening a notebook restores a
// finished result too, and the hint would then point at a cell nobody just ran.
if (runStatus === 'done' && previousRunStatus !== 'done') {
reportRunFinished(nodeId, !!returnVariable)
}
}, [runStatus, returnVariable, nodeId, reportRunFinished])
// Page 1 at the default size comes straight from the envelope; other pages re-query CH.
const dataframeResult = useMemo(() => {
if (pageResult) {
return toDataframeResult({
columns: pageResult.columns,
row_count: pageResult.rows.length,
first_page: pageResult.rows,
})
}
return result ? toDataframeResult(result) : null
}, [pageResult, result])
const hasMorePages = pageResult
? pageResult.has_more
: (result?.has_more ?? (result?.first_page ?? []).length >= SQL_V2_DEFAULT_PAGE_SIZE)
const cachedResults = useMemo(() => (result ? toCachedResults(result) : null), [result])
const activeTab = attributes.outputTab === OutputTab.Results ? OutputTab.Results : OutputTab.Visualization
// The stored viz config wins, but the source always tracks the node's current code — and the
// connection it runs on, so anything the viz layer re-queries lands on the same engine.
const vizQuery = useMemo((): DataVisualizationNode => {
const query: DataVisualizationNode = {
kind: NodeKind.DataVisualizationNode,
display: ChartDisplayType.Auto,
...attributes.vizQuery,
source: {
kind: NodeKind.HogQLQuery,
query: attributes.code,
connectionId: attributes.connectionId ?? undefined,
sendRawQuery: attributes.connectionId ? !!attributes.sendRawQuery || undefined : undefined,
},
}
if (cachedResults) {
query.chartSettings = applyVisualizationType(
query,
query.display ?? ChartDisplayType.Auto,
columnsFromResponse(cachedResults),
cachedResults.results.length
).chartSettings
}
return query
}, [attributes.vizQuery, attributes.code, attributes.connectionId, attributes.sendRawQuery, cachedResults])
// Grow a still-too-short node to fit the result each run lands, so output is readable without
// a manual resize. Sized to the rows that came back — a scalar stays compact, a wide result
// grows up to a cap. Only grows, and only for a run we haven't sized yet, so a deliberate
// resize is left untouched.
const sizedRunIdRef = useRef<string | null | undefined>(
initialSizedRunId({ hasResult: !!result, height: attributes.height, runId: attributes.runId ?? null })
)
useEffect(() => {
const runId = attributes.runId ?? null
// A read-only notebook lays the node out from its content, so there is no fixed height to
// outgrow — and no editor to persist one into.
if (!result || !isEditable || isJupyterMode || runId === sizedRunIdRef.current) {
return
}
sizedRunIdRef.current = runId
const target =
activeTab === OutputTab.Visualization
? VIZ_MIN_HEIGHT
: outputHeightForShape({ rowCount: (result.first_page ?? []).length })
if (target !== null && (typeof attributes.height !== 'number' || attributes.height < target)) {
updateAttributes({ height: target })
}
// oxlint-disable-next-line exhaustive-deps
}, [result, attributes.runId, isEditable])
const visualization = (
<Query
// Keyed per run so a fresh envelope re-seeds the cached response.
// The SQLEditor prefix opts into container-governed chart sizing
// (dataVisualizationLogic.presetChartHeight) — without it charts
// render at 60vh, dwarfing the node.
uniqueKey={`SQLEditor-notebook-sqlv2-${nodeId}-${attributes.runId ?? 'initial'}`}
query={vizQuery}
setQuery={(query) => {
// DataVisualization pushes default settings during its render;
// defer the doc write so we don't update Tiptap mid-render.
const vizQuery = query as DataVisualizationNode
setTimeout(() => updateAttributes({ vizQuery }), 0)
}}
cachedResults={cachedResults ?? undefined}
attachTo={notebookLogic}
/>
)
if (!expanded) {
return null
}
if (isJupyterMode) {
const showsChart = activeTab === OutputTab.Visualization && !!cachedResults
return (
<NotebookJupyterCellOutput
result={result}
dataframeResult={dataframeResult && result?.columns?.length ? dataframeResult : null}
dataframeProps={{
page,
pageSize,
hasMore: hasMorePages,
loading: isRunning || pageLoading || (isRestoringResult && !result?.first_page?.length),
paginationDisabledReason: pageLoading
? 'Fetching page…'
: isRunning
? 'Query is running'
: (operationBlockReason ?? undefined),
onNextPage: () => setPage(page + 1),
onPreviousPage: () => setPage(page - 1),
}}
runError={runError}
status={
isRunning && pendingKernelStart
? 'Starting compute sandbox…'
: resultRestoreUnavailable
? 'Run the cell again to see its full output.'
: isRestoringResult
? 'Loading saved output…'
: null
}
executionCount={executionCounts[nodeId] ?? null}
returnVariable={returnVariable}
onReturnVariableChange={(returnVariable) => updateAttributes({ returnVariable })}
isEditable={isEditable}
valueOverride={showsChart ? <div className="h-80 flex flex-col">{visualization}</div> : undefined}
footerExtra={
cachedResults ? (
<LemonButton
size="xsmall"
onClick={() =>
updateAttributes({
outputTab: showsChart ? OutputTab.Results : OutputTab.Visualization,
})
}
data-attr="notebook-jupyter-sql-toggle-chart"
>
{showsChart ? 'Show table' : 'Show chart'}
</LemonButton>
) : null
}
/>
)
}
return (
<div data-attr="notebook-node-sql-v2" className="flex h-full min-h-0 flex-col">
<div
className="flex min-h-0 flex-1 flex-col"
onMouseDown={(event) => event.stopPropagation()}
onDragStart={(event) => event.stopPropagation()}
>
{isStale ? (
<div className="shrink-0 pb-2" onClick={(event) => event.stopPropagation()}>
<NotebookStaleCellBanner reason={staleReason ?? undefined} />
</div>
) : staleDownstreamCount > 0 && !isChainRunning ? (
<div className="shrink-0 pb-2" onClick={(event) => event.stopPropagation()}>
<NotebookRunDownstreamBanner
count={staleDownstreamCount}
onRun={() => runStaleChain(notebookLogic.values.content ?? null, nodeId)}
disabledReason={isRunning ? 'This cell is running' : (operationBlockReason ?? undefined)}
/>
</div>
) : null}
{isRunning && pendingKernelStart ? (
<div className="shrink-0 px-2 pt-1 pb-2 text-xs text-muted">Starting compute sandbox…</div>
) : null}
{resultRestoreUnavailable ? (
<div className="p-2 text-xs text-muted">Run the cell again to see its full results.</div>
) : isRestoringResult ? (
<div className="p-2 text-xs text-muted">Loading saved results…</div>
) : null}
{runError ? (
<div className="p-2 text-xs font-mono text-danger whitespace-pre-wrap">{runError}</div>
) : dataframeResult && cachedResults ? (
<>
<NotebookCellOutputHeader>
<LemonTabs
size="small"
activeKey={activeTab}
onChange={(tab) => {
// Charts need vertical room; the default node height only fits a few table rows.
const height =
tab === OutputTab.Visualization &&
(typeof attributes.height !== 'number' || attributes.height < VIZ_MIN_HEIGHT)
? VIZ_MIN_HEIGHT
: attributes.height
updateAttributes({ outputTab: tab, height })
}}
// The strip already carries the bottom border, and the bar draws
// its own as a ::before, so two 1px lines stack without this.
barClassName="mb-0 before:hidden"
tabs={[
{ key: OutputTab.Results, label: 'Results' },
{ key: OutputTab.Visualization, label: 'Visualization' },
]}
/>
</NotebookCellOutputHeader>
{activeTab === OutputTab.Results ? (
<div className="min-h-0 flex-1 overflow-y-auto">
<NotebookDataframeTable
result={dataframeResult}
loading={
isRunning || pageLoading || (isRestoringResult && !result?.first_page?.length)
}
page={page}
pageSize={pageSize}
hasMore={hasMorePages}
// Wide text columns (long strings, JSON blobs) shouldn't make every
// row tall; clamp to one line here and let the user open a cell.
truncateCells
// Serialize page fetches: no new page while one is in flight, a run
// is replacing this result, or another cell's operation is running.
paginationDisabledReason={
pageLoading
? 'Fetching page…'
: isRunning
? 'Query is running'
: (operationBlockReason ?? undefined)
}
onNextPage={() => setPage(page + 1)}
onPreviousPage={() => setPage(page - 1)}
onPageSizeChange={setPageSize}
/>
</div>
) : (
<div
className="px-2 py-2 flex min-h-0 flex-1 flex-col overflow-hidden"
onClick={(event) => event.stopPropagation()}
>
{visualization}
</div>
)}
</>
) : (
<div className="text-xs text-muted font-mono p-2">Run the query to see execution results.</div>
)}
</div>
<NotebookCellOutputNameFooter
returnVariable={returnVariable}
onChange={(returnVariable) => updateAttributes({ returnVariable })}
inputRef={setReturnVariableInput}
>
<NotebookDataframeHintPopover
nodeId={nodeId}
notebookShortId={notebookShortId}
referenceElement={returnVariableInput}
/>
{returnVariableError ? <span className="text-danger">{returnVariableError}</span> : null}
{sqlV2ReturnVariableUsage.length > 0 ? (
<span className="text-muted">
Used in{' '}
{sqlV2ReturnVariableUsage.map((usage) => (
<button
key={usage.nodeId}
type="button"
className="text-muted hover:text-default underline underline-offset-2 ml-1"
onClick={() => navigateToNode(usage.nodeId)}
>
{usageLabel(usage.nodeIndex, usage.title)}
</button>
))}
</span>
) : null}
</NotebookCellOutputNameFooter>
</div>
)
}
const Settings = ({
attributes,
updateAttributes,
}: NotebookNodeAttributeProperties<NotebookNodeSQLV2Attributes>): JSX.Element => {
const nodeLogic = useMountedLogic(notebookNodeLogic)
const { nodeId, notebookLogic } = useValues(nodeLogic)
const dataLogic = notebookCodeCellLogic(nodeId, notebookLogic, attributes, updateAttributes)
const { isRunning } = useValues(dataLogic)
const { runNode } = useActions(dataLogic)
return (
<NotebookCodeSQLEditorSettings
attributes={attributes}
updateAttributes={updateAttributes}
tabIdSuffix="datav2"
persistConnection
// The cell runs from the notebook's own top row, so the editor keeps only its Cmd+Enter
// binding — which hands over what the editor holds, since the document catches up to a
// keystroke or a just-picked connection a render later.
hideRunButton
onRunQuery={(code, connection) => runNode({ code, ...connection })}
runQueryLoading={isRunning}
/>
)
}
export const NotebookNodeSQLV2 = createPostHogWidgetNode<NotebookNodeSQLV2Attributes>({
nodeType: NotebookNodeType.SQLV2,
titlePlaceholder: 'SQL',
Component,
heightEstimate: 120,
minHeight: 80,
resizeable: true,
startExpanded: true,
attributes: {
code: {
default: '',
},
returnVariable: {
default: 'sql_df',
},
connectionId: {
default: null,
},
sendRawQuery: {
default: false,
},
runId: {
default: null,
},
result: {
default: null,
},
runStatus: {
default: null,
},
outputTab: {
default: OutputTab.Visualization,
},
vizQuery: {
default: null,
},
},
Settings,
settingsPlacement: 'inline',
serializedText: (attrs) => attrs.code,
})