You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
344 lines
10 KiB
Elixir
344 lines
10 KiB
Elixir
defmodule Plausible.Imported.CSVImporter do
|
|
@moduledoc """
|
|
CSV importer from either S3 for which it uses ClickHouse [s3 table function](https://clickhouse.com/docs/en/sql-reference/table-functions/s3)
|
|
or from local storage for which it uses [input function.](https://clickhouse.com/docs/en/sql-reference/table-functions/input)
|
|
"""
|
|
|
|
use Plausible.Imported.Importer
|
|
|
|
@impl true
|
|
def name(), do: :csv
|
|
|
|
@impl true
|
|
def label(), do: "CSV"
|
|
|
|
# NOTE: change it once CSV import is implemented
|
|
@impl true
|
|
def email_template(), do: "google_analytics_import.html"
|
|
|
|
@impl true
|
|
def parse_args(%{"uploads" => uploads, "storage" => storage}) do
|
|
[uploads: uploads, storage: storage]
|
|
end
|
|
|
|
@impl true
|
|
def import_data(site_import, opts) do
|
|
storage = Keyword.fetch!(opts, :storage)
|
|
uploads = Keyword.fetch!(opts, :uploads)
|
|
|
|
if storage == "local" do
|
|
# we need to remove the imported files from local storage
|
|
# after the importer has completed or ran out of attempts
|
|
paths = Enum.map(uploads, &Map.fetch!(&1, "local_path"))
|
|
|
|
Oban.insert!(
|
|
Plausible.Workers.LocalImportAnalyticsCleaner.new(
|
|
%{"import_id" => site_import.id, "paths" => paths},
|
|
schedule_in: _one_hour = 3600
|
|
)
|
|
)
|
|
end
|
|
|
|
{:ok, ch} =
|
|
Plausible.IngestRepo.config()
|
|
|> Keyword.replace!(:pool_size, 1)
|
|
|> Ch.start_link()
|
|
|
|
case storage do
|
|
"s3" -> import_s3(ch, site_import, uploads)
|
|
"local" -> import_local(ch, site_import, uploads)
|
|
end
|
|
rescue
|
|
# we are cancelling on any argument or ClickHouse errors, assuming they are permanent
|
|
e in [ArgumentError, Ch.Error] ->
|
|
# see Plausible.Imported.Importer for more details on transient vs permanent errors
|
|
{:error, Exception.message(e)}
|
|
end
|
|
|
|
defp import_s3(ch, site_import, uploads) do
|
|
%{
|
|
id: import_id,
|
|
site_id: site_id,
|
|
start_date: start_date,
|
|
end_date: end_date
|
|
} = site_import
|
|
|
|
%{access_key_id: s3_access_key_id, secret_access_key: s3_secret_access_key} =
|
|
Plausible.S3.import_clickhouse_credentials()
|
|
|
|
Enum.each(uploads, fn upload ->
|
|
%{"filename" => filename, "s3_url" => s3_url} = upload
|
|
|
|
{table, _, _} = parse_filename!(filename)
|
|
s3_structure = input_structure!(table)
|
|
s3_columns = input_columns!(table)
|
|
|
|
statement =
|
|
"""
|
|
INSERT INTO {table:Identifier}(site_id,import_id,#{s3_columns}) \
|
|
SELECT {site_id:UInt64}, {import_id:UInt64}, * \
|
|
FROM s3({s3_url:String},{s3_access_key_id:String},{s3_secret_access_key:String},{s3_format:String},{s3_structure:String}) \
|
|
WHERE date >= {start_date:Date} AND date <= {end_date:Date}\
|
|
"""
|
|
|
|
params =
|
|
%{
|
|
"table" => table,
|
|
"site_id" => site_id,
|
|
"import_id" => import_id,
|
|
"s3_url" => s3_url,
|
|
"s3_access_key_id" => s3_access_key_id,
|
|
"s3_secret_access_key" => s3_secret_access_key,
|
|
"s3_format" => "CSVWithNames",
|
|
"s3_structure" => s3_structure,
|
|
"start_date" => start_date,
|
|
"end_date" => end_date
|
|
}
|
|
|
|
Ch.query!(ch, statement, params, timeout: :infinity)
|
|
end)
|
|
end
|
|
|
|
defp import_local(ch, site_import, uploads) do
|
|
%{
|
|
id: import_id,
|
|
site_id: site_id,
|
|
start_date: start_date,
|
|
end_date: end_date
|
|
} = site_import
|
|
|
|
DBConnection.run(
|
|
ch,
|
|
fn conn ->
|
|
Enum.each(uploads, fn upload ->
|
|
%{"filename" => filename, "local_path" => local_path} = upload
|
|
|
|
{table, _, _} = parse_filename!(filename)
|
|
input_structure = input_structure!(table)
|
|
input_columns = input_columns!(table)
|
|
|
|
statement =
|
|
"""
|
|
INSERT INTO {table:Identifier}(site_id,import_id,#{input_columns}) \
|
|
SELECT {site_id:UInt64}, {import_id:UInt64}, * \
|
|
FROM input({input_structure:String}) \
|
|
WHERE date >= {start_date:Date} AND date <= {end_date:Date} \
|
|
FORMAT CSVWithNames\
|
|
"""
|
|
|
|
params = %{
|
|
"table" => table,
|
|
"site_id" => site_id,
|
|
"import_id" => import_id,
|
|
"input_structure" => input_structure,
|
|
"start_date" => start_date,
|
|
"end_date" => end_date
|
|
}
|
|
|
|
# we are reading in 512KB chunks for better performance
|
|
# the default would've been line by line (not great for a CSV)
|
|
File.stream!(local_path, 512_000)
|
|
|> Stream.into(Ch.stream(conn, statement, params))
|
|
|> Stream.run()
|
|
end)
|
|
end,
|
|
timeout: :infinity
|
|
)
|
|
end
|
|
|
|
input_structures = %{
|
|
"imported_browsers" =>
|
|
"date Date, browser String, browser_version String, visitors UInt64, visits UInt64, visit_duration UInt64, bounces UInt32, pageviews UInt64",
|
|
"imported_devices" =>
|
|
"date Date, device String, visitors UInt64, visits UInt64, visit_duration UInt64, bounces UInt32, pageviews UInt64",
|
|
"imported_entry_pages" =>
|
|
"date Date, entry_page String, visitors UInt64, entrances UInt64, visit_duration UInt64, bounces UInt32, pageviews UInt64",
|
|
"imported_exit_pages" =>
|
|
"date Date, exit_page String, visitors UInt64, visit_duration UInt64, exits UInt64, bounces UInt32, pageviews UInt64",
|
|
"imported_locations" =>
|
|
"date Date, country String, region String, city UInt64, visitors UInt64, visits UInt64, visit_duration UInt64, bounces UInt32, pageviews UInt64",
|
|
"imported_operating_systems" =>
|
|
"date Date, operating_system String, operating_system_version String, visitors UInt64, visits UInt64, visit_duration UInt64, bounces UInt32, pageviews UInt64",
|
|
"imported_pages" =>
|
|
"date Date, hostname String, page String, visits UInt64, visitors UInt64, pageviews UInt64, exits UInt64, time_on_page UInt64",
|
|
"imported_sources" =>
|
|
"date Date, source String, referrer String, utm_source String, utm_medium String, utm_campaign String, utm_content String, utm_term String, pageviews UInt64, visitors UInt64, visits UInt64, visit_duration UInt64, bounces UInt32",
|
|
"imported_visitors" =>
|
|
"date Date, visitors UInt64, pageviews UInt64, bounces UInt64, visits UInt64, visit_duration UInt64"
|
|
}
|
|
|
|
@doc """
|
|
Extracts min/max date range from a list of uploads.
|
|
|
|
Examples:
|
|
|
|
iex> date_range([
|
|
...> %{"filename" => "imported_devices_20190101_20210101.csv"},
|
|
...> "pages_20200101_20220101.csv"
|
|
...> ])
|
|
Date.range(~D[2019-01-01], ~D[2022-01-01])
|
|
|
|
iex> date_range([])
|
|
nil
|
|
|
|
"""
|
|
@spec date_range([String.t() | %{String.t() => String.t()}, ...]) :: Date.Range.t() | nil
|
|
def date_range([_ | _] = uploads), do: date_range(uploads, _start_date = nil, _end_date = nil)
|
|
def date_range([]), do: nil
|
|
|
|
defp date_range([upload | uploads], prev_start_date, prev_end_date) do
|
|
filename =
|
|
case upload do
|
|
%{"filename" => filename} -> filename
|
|
filename when is_binary(filename) -> filename
|
|
end
|
|
|
|
{_table, start_date, end_date} = parse_filename!(filename)
|
|
|
|
start_date =
|
|
if prev_start_date do
|
|
Enum.min([start_date, prev_start_date], Date)
|
|
else
|
|
start_date
|
|
end
|
|
|
|
end_date =
|
|
if prev_end_date do
|
|
Enum.max([end_date, prev_end_date], Date)
|
|
else
|
|
end_date
|
|
end
|
|
|
|
date_range(uploads, start_date, end_date)
|
|
end
|
|
|
|
defp date_range([], first, last), do: Date.range(first, last)
|
|
|
|
@spec parse_date!(String.t()) :: Date.t()
|
|
defp parse_date!(date) do
|
|
date |> Timex.parse!("{YYYY}{0M}{0D}") |> NaiveDateTime.to_date()
|
|
end
|
|
|
|
@doc """
|
|
Extracts table name and min/max dates from the filename.
|
|
|
|
Examples:
|
|
|
|
iex> parse_filename!("my_data.csv")
|
|
** (ArgumentError) invalid filename
|
|
|
|
iex> parse_filename!("imported_devices_00010101_20250101.csv")
|
|
{"imported_devices", ~D[0001-01-01], ~D[2025-01-01]}
|
|
|
|
iex> parse_filename!("devices_00010101_20250101.csv")
|
|
{"imported_devices", ~D[0001-01-01], ~D[2025-01-01]}
|
|
|
|
"""
|
|
@spec parse_filename!(String.t()) ::
|
|
{table :: String.t(), start_date :: Date.t(), end_date :: Date.t()}
|
|
def parse_filename!(filename)
|
|
|
|
for {table, input_structure} <- input_structures do
|
|
defp input_structure!(unquote(table)), do: unquote(input_structure)
|
|
|
|
input_columns =
|
|
input_structure
|
|
|> String.split(",", trim: true)
|
|
|> Enum.map_join(",", fn kv ->
|
|
[col, _type] = String.split(kv)
|
|
String.trim(col)
|
|
end)
|
|
|
|
defp input_columns!(unquote(table)), do: unquote(input_columns)
|
|
|
|
def parse_filename!(
|
|
<<unquote(table)::bytes, ?_, start_date::8-bytes, ?_, end_date::8-bytes, ".csv">>
|
|
) do
|
|
{unquote(table), parse_date!(start_date), parse_date!(end_date)}
|
|
end
|
|
|
|
"imported_" <> name = table
|
|
|
|
def parse_filename!(
|
|
<<unquote(name)::bytes, ?_, start_date::8-bytes, ?_, end_date::8-bytes, ".csv">>
|
|
) do
|
|
{unquote(table), parse_date!(start_date), parse_date!(end_date)}
|
|
end
|
|
end
|
|
|
|
def parse_filename!(_filename) do
|
|
raise ArgumentError, "invalid filename"
|
|
end
|
|
|
|
@doc """
|
|
Checks if the provided filename conforms to the expected format.
|
|
|
|
Examples:
|
|
|
|
iex> valid_filename?("my_data.csv")
|
|
false
|
|
|
|
iex> valid_filename?("imported_devices_00010101_20250101.csv")
|
|
true
|
|
|
|
iex> valid_filename?("devices_00010101_20250101.csv")
|
|
true
|
|
|
|
"""
|
|
@spec valid_filename?(String.t()) :: boolean
|
|
def valid_filename?(filename) do
|
|
try do
|
|
parse_filename!(filename)
|
|
else
|
|
_ -> true
|
|
rescue
|
|
_ -> false
|
|
end
|
|
end
|
|
|
|
@doc """
|
|
Extracts the table name from the provided filename.
|
|
|
|
Raises if the filename doesn't conform to the expected format.
|
|
|
|
Examples:
|
|
|
|
iex> extract_table("my_data.csv")
|
|
** (ArgumentError) invalid filename
|
|
|
|
iex> extract_table("imported_devices_00010101_20250101.csv")
|
|
"imported_devices"
|
|
|
|
iex> extract_table("devices_00010101_20250101.csv")
|
|
"imported_devices"
|
|
|
|
"""
|
|
@spec extract_table(String.t()) :: String.t()
|
|
def extract_table(filename) do
|
|
{table, _start_date, _end_date} = parse_filename!(filename)
|
|
table
|
|
end
|
|
|
|
@doc """
|
|
Returns local directory for CSV imports storage.
|
|
|
|
Builds upon `$PERSISTENT_CACHE_DIR` (if set) and falls back to /tmp
|
|
|
|
Examples:
|
|
|
|
iex> local_dir = local_dir(_site_id = 37)
|
|
iex> String.ends_with?(local_dir, "/plausible-imports/37")
|
|
true
|
|
|
|
"""
|
|
def local_dir(site_id) do
|
|
persistent_cache_dir = Application.get_env(:plausible, :persistent_cache_dir)
|
|
|
|
Path.join([
|
|
persistent_cache_dir || System.tmp_dir!(),
|
|
"plausible-imports",
|
|
Integer.to_string(site_id)
|
|
])
|
|
end
|
|
end
|