📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!
TypeScript chartsclickhousedockergdprgoogle-analyticsmetricsmonitoringmysqlnestjsprivacyredisself-hostedselfhostedstatisticstailwindcssweb-analyticswebanalyticswebsite
import {
Injectable,
BadRequestException,
NotFoundException,
Logger,
} from '@nestjs/common'
import { randomUUID } from 'crypto'
import {
DataImport,
DataImportStatus,
MAX_IMPORT_ID,
} from './entity/data-import.entity'
import { clickhouse } from '../common/integrations/clickhouse'
import { redis } from '../common/constants'
const CLICKHOUSE_DB = process.env.CLICKHOUSE_DATABASE || 'analytics'
const IMPORT_IN_PROGRESS_ERROR =
'An import is already in progress for this project. Please wait for it to complete before starting a new one.'
const IMPORT_CREATE_LOCK_TTL_MS = 10_000
const IMPORT_CREATE_LOCK_WAIT_MS = 5_000
const IMPORT_CREATE_LOCK_RETRY_MS = 100
const CLICKHOUSE_DATE_TIME_REGEX = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/
const ISO_DATE_TIME_REGEX =
/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(?:\.\d+)?(?:Z|[+-]\d{2}:\d{2})?$/
function formatClickHouseDateTime(value: Date | string): string {
if (value instanceof Date) {
return value.toISOString().replace('T', ' ').replace('Z', '').slice(0, 19)
}
if (CLICKHOUSE_DATE_TIME_REGEX.test(value)) {
return value
}
if (ISO_DATE_TIME_REGEX.test(value)) {
const date = new Date(value)
if (!Number.isNaN(date.getTime())) {
return date.toISOString().replace('T', ' ').replace('Z', '').slice(0, 19)
}
}
throw new Error('Invalid datetime input')
}
function formatNullableClickHouseDateTime(
value: Date | string | null | undefined,
): string | null {
if (!value) {
return null
}
return formatClickHouseDateTime(value)
}
@Injectable()
export class DataImportService {
private readonly logger = new Logger(DataImportService.name)
private getCreateLockKey(projectId: string): string {
return `data-import:create:${projectId}`
}
private async acquireCreateLock(projectId: string): Promise<string> {
const token = randomUUID()
const lockKey = this.getCreateLockKey(projectId)
const deadline = Date.now() + IMPORT_CREATE_LOCK_WAIT_MS
while (Date.now() < deadline) {
const acquired = await redis.set(
lockKey,
token,
'PX',
IMPORT_CREATE_LOCK_TTL_MS,
'NX',
)
if (acquired === 'OK') {
return token
}
await new Promise((resolve) =>
setTimeout(resolve, IMPORT_CREATE_LOCK_RETRY_MS),
)
}
throw new BadRequestException(IMPORT_IN_PROGRESS_ERROR)
}
private async releaseCreateLock(
projectId: string,
token: string,
): Promise<void> {
await redis.eval(
`if redis.call("GET", KEYS[1]) == ARGV[1] then
return redis.call("DEL", KEYS[1])
end
return 0`,
1,
this.getCreateLockKey(projectId),
token,
)
}
private async getNextId(projectId: string): Promise<number> {
const result = await clickhouse
.query({
query: `SELECT id FROM ${CLICKHOUSE_DB}.data_import FINAL WHERE projectId = {projectId:FixedString(12)} ORDER BY id ASC`,
query_params: { projectId },
})
.then((rs) => rs.json<{ id: number | string }>())
let next = 1
for (const row of result.data || []) {
const currentId = Number(row.id)
if (currentId < next) {
continue
}
if (currentId === next) {
next += 1
continue
}
break
}
if (next > MAX_IMPORT_ID) {
throw new BadRequestException(
`This project has reached the maximum number of imports (${MAX_IMPORT_ID}). Please delete an old import before creating a new one.`,
)
}
return next
}
async create(projectId: string, provider: string): Promise<DataImport> {
const lockToken = await this.acquireCreateLock(projectId)
try {
const active = await this.findActiveByProject(projectId)
if (active) {
throw new BadRequestException(IMPORT_IN_PROGRESS_ERROR)
}
const id = await this.getNextId(projectId)
const now = formatClickHouseDateTime(new Date())
await clickhouse.insert({
table: `${CLICKHOUSE_DB}.data_import`,
values: [
{
id,
projectId,
provider,
status: 'pending',
dateFrom: null,
dateTo: null,
totalRows: 0,
importedRows: 0,
invalidRows: 0,
errorMessage: null,
createdAt: now,
finishedAt: null,
version: 1,
},
],
format: 'JSONEachRow',
})
return {
id,
projectId,
provider,
status: DataImportStatus.PENDING,
dateFrom: null,
dateTo: null,
totalRows: 0,
importedRows: 0,
invalidRows: 0,
errorMessage: null,
createdAt: now,
finishedAt: null,
version: 1,
}
} finally {
await this.releaseCreateLock(projectId, lockToken)
}
}
private async findActiveByProject(
projectId: string,
): Promise<DataImport | null> {
const result = await clickhouse
.query({
query: `SELECT * FROM ${CLICKHOUSE_DB}.data_import FINAL WHERE projectId = {projectId:FixedString(12)} AND status IN ('pending', 'processing') LIMIT 1`,
query_params: { projectId },
})
.then((rs) => rs.json<DataImport>())
return result.data?.[0] || null
}
async findAllByProject(projectId: string): Promise<DataImport[]> {
const result = await clickhouse
.query({
query: `SELECT * FROM ${CLICKHOUSE_DB}.data_import FINAL WHERE projectId = {projectId:FixedString(12)} ORDER BY createdAt DESC`,
query_params: { projectId },
})
.then((rs) => rs.json<DataImport>())
return result.data || []
}
async findOne(projectId: string, importId: number): Promise<DataImport> {
const result = await clickhouse
.query({
query: `SELECT * FROM ${CLICKHOUSE_DB}.data_import FINAL WHERE projectId = {projectId:FixedString(12)} AND id = {id:UInt8} LIMIT 1`,
query_params: { projectId, id: importId },
})
.then((rs) => rs.json<DataImport>())
const dataImport = result.data?.[0]
if (!dataImport) {
throw new NotFoundException('Import not found')
}
return dataImport
}
async updateStatus(
id: number,
projectId: string,
updates: Partial<DataImport>,
): Promise<void> {
const existing = await this.findOne(projectId, id)
const nextVersion = (existing.version || 0) + 1
await clickhouse.insert({
table: `${CLICKHOUSE_DB}.data_import`,
values: [
{
...existing,
...updates,
id,
projectId,
createdAt: formatClickHouseDateTime(
updates.createdAt ?? existing.createdAt,
),
finishedAt: formatNullableClickHouseDateTime(
updates.finishedAt ?? existing.finishedAt,
),
version: nextVersion,
},
],
format: 'JSONEachRow',
})
}
async markProcessing(id: number, projectId: string): Promise<void> {
await this.updateStatus(id, projectId, {
status: DataImportStatus.PROCESSING,
})
}
async markCompleted(
id: number,
projectId: string,
stats: {
importedRows: number
invalidRows: number
totalRows: number
dateFrom: string | null
dateTo: string | null
},
): Promise<void> {
const now = formatClickHouseDateTime(new Date())
await this.updateStatus(id, projectId, {
status: DataImportStatus.COMPLETED,
importedRows: stats.importedRows,
invalidRows: stats.invalidRows,
totalRows: stats.totalRows,
dateFrom: stats.dateFrom,
dateTo: stats.dateTo,
finishedAt: now,
})
}
async markFailed(
id: number,
projectId: string,
errorMessage: string,
): Promise<void> {
const now = formatClickHouseDateTime(new Date())
await this.updateStatus(id, projectId, {
status: DataImportStatus.FAILED,
errorMessage,
finishedAt: now,
})
}
async cleanupImportedRows(
projectId: string,
importId: number,
): Promise<void> {
await clickhouse.command({
query: `ALTER TABLE ${CLICKHOUSE_DB}.events DELETE WHERE pid = {pid:FixedString(12)} AND importID = {importID:UInt8} AND type IN ('pageview', 'custom_event', 'outbound')`,
query_params: { pid: projectId, importID: importId },
clickhouse_settings: { mutations_sync: '2' },
})
}
async deleteImport(projectId: string, importId: number): Promise<void> {
const dataImport = await this.findOne(projectId, importId)
if (
dataImport.status === DataImportStatus.PENDING ||
dataImport.status === DataImportStatus.PROCESSING
) {
throw new BadRequestException(
'Cannot delete an import that is still in progress.',
)
}
try {
await this.cleanupImportedRows(projectId, importId)
} catch (error) {
this.logger.error(
`Failed to delete imported data from ClickHouse for import ${importId}: ${error.message}`,
)
throw new BadRequestException(
'Failed to remove imported data. Please try again later.',
)
}
await this.deleteImportRecord(projectId, importId)
}
async deleteImportRecord(projectId: string, importId: number): Promise<void> {
await clickhouse.command({
query: `ALTER TABLE ${CLICKHOUSE_DB}.data_import DELETE WHERE projectId = {projectId:FixedString(12)} AND id = {id:UInt8}`,
query_params: { projectId, id: importId },
})
}
async hasImportedDataInRange(
projectId: string,
from: string,
to: string,
): Promise<boolean> {
const result = await clickhouse
.query({
query: `SELECT count() as cnt FROM ${CLICKHOUSE_DB}.events WHERE pid = {pid:FixedString(12)} AND type = 'pageview' AND importID IS NOT NULL AND created >= {from:DateTime} AND created <= {to:DateTime} LIMIT 1`,
query_params: { pid: projectId, from, to },
})
.then((rs) => rs.json<{ cnt: string }>())
return Number(result.data?.[0]?.cnt) > 0
}
}