问题[csv](dba)

产品名称

OceanBase V4.2.5-社区版本

问题描述

obd部署集群：
三台机器1-1-1，16c64G，memory_limit=32G

导入csv文件，1亿条数据，按时间字段分区100个，未设置主键

我使用这种方法导入数据：
load data/ +parallel(9)load_batch_size(18) /

结果非常耗时

如何处理才能快速导入一亿条数据？

系统内置租户设置：

alter system set system_memory=‘15g’; 
alter resource unit sys_unit_config max_memory=‘15g’,min_memory=‘15g’; 
#Tuning Parameters 
alter system set enable_merge_by_turn= False; 
alter system set trace_log_slow_query_watermark=‘100s’; 
alter system set max_kept_major_version_number=1; 
alter system set enable_sql_operator_dump=True; 
alter system set _hash_area_size=‘3g’; 
alter system set memstore_limit_percentage=50; 
alter system set enable_rebalance=False; 
alter system set memory_chunk_cache_size=‘1g’; 
alter system set minor_freeze_times=5; 
alter system set merge_thread_count=20; 
alter system set cache_wash_threshold=‘30g’; 
alter system set _ob_enable_prepared_statement=true; 
##Adjust the log level and number of saved logs 
alter system set syslog_level=‘PERF’; 
alter system set max_syslog_file_count=100; 
alter system set enable_syslog_recycle=‘True’; 

自定义租户设置：

CREATE RESOURCE UNIT unit1 max_cpu = 9,max_memory = 3006477108,min_memory = 3006477108, max_iops = 10000,min_iops = 1280,max_session_num = 3000,max_disk_size = 214748364800 – 200 GB; 
set global NLS_DATE_FORMAT=‘YYYY-MM-DD HH24:MI:SS’; 
set global NLS_TIMESTAMP_FORMAT=‘YYYY-MM-DD HH24:MI:SS.FF’; 
set global NLS_TIMESTAMP_TZ_FORMAT=‘YYYY-MM-DD HH24:MI:SS.FF TZR TZD’; 
set global ob_sql_work_area_percentage=80; 
set global optimizer_use_sql_plan_baselines = true; 
set global optimizer_capture_sql_plan_baselines = true; 
alter system set ob_enable_batched_multi_statement=‘true’; 
##Set under the tenant to prevent transaction timeout
show variables like ‘%timeout%’; 
set global ob_query_timeout=72000000000; 
set global ob_trx_timeout=72000000000; 
set global max_allowed_packet=67108864; 
#Execute load data permission
set global secure_file_priv=’’; 
grant file on *.* to sqluser01;

我定期从 httparchive.org 导入数据。数据是一个 MySQL CSV 导出，我使用 pgloader，它处理这个导出的怪癖（\Nfor NULL）等。我还需要做一些额外的处理以进行规范化：

• 在协议 (http|https) 和主机部分中拆分 url
• 将字符串日期“Mon DD YYYY”转换为日期对象

目前，我在导入数据时有一些触发器可以执行此操作，但我正在寻找改进方法，特别是查看是否可以并行运行某些步骤。

我有以下用于提取协议和端口的 CTE：

with split as
(select regexp_match(url, '(https|http)://(.+)/' )as parts 
from urls )

在本地运行这似乎比tsdebug

这适用于选择，但作为更新似乎非常慢。

with split as
(select regexp_match(url, '(https|http)://(.+)/' )as parts 
from urls )
update urls
set 
protocol = parts[1],
host = parts[2] 
from split

另一种方法，尤其是在处理文本源时，会在 URL 进入 Postgres 之前对其进行拆分。

未压缩的 CSV 为 3526430884 字节，导入大约需要 20 分钟，无需处理。但这与加工相比是两倍多。FWIW 我也尝试过使用外部数据包装器。但是，即使在使用 CSV（空值、编码）解决了各种问题之后，这也会导致内存错误。

在一些帮助下，我设法运行了基准测试并改进了我的触发器。

CREATE OR REPLACE FUNCTION public.extract_protocol()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE elements text [];
BEGIN
elements := regexp_match(NEW.url, '(https|http)://(.+)/');
NEW.protocol = elements[1];
NEW.host = elements[2];
RETURN NEW;
END;
$function$

现在，这比进行后续更新运行得更快，但两者都不是限制因素。现在的瓶颈是在将清理后的数据插入主表时索引的开销。我认为我唯一的选择是权衡插入索引的成本，而不是禁用然后添加它们。

我想使用复制表的 5 列进行导出，其中 3 列包含 ID、文本和时间戳值，但其中 2 列包含 JSON 和 GeoJSON 元素，例如：

 // Please, note the non-quoted string Value1
 // when there is no space in the value itself:
{"key": "value with space", "other_key": Value1, ...}

但是当使用这个命令时：

psql \
  -d <connection_uri> \
  --command "\copy public.mytable (id, json_array, geojson_field, status, timestamp) TO './file.csv' DELIMITER ';' CSV HEADER ENCODING 'UTF8' QUOTE '\"' ;"

结果看起来很奇怪：

11784,"{'"First value'",'"Second value'", ThirdValue, '"Fourth val'",...

即它以奇怪的顺序放置引号：“single-double <field_value> single-double”

（我也没有得到标题......）

我希望我可以在 csv 文件中以一个真正的 JSON 对象结束，我可以复制/粘贴到验证器中，例如这里没有错误。

难道我做错了什么？
如果是，如何解决？

PG:13

我正在尝试将一个简单CSV文件导入 SQL Server 2017 上的表中。CSV 文件和表在列数和数据类型方面相互匹配。这是CSV文件的摘录：

UID,customerID,DateT,TypeID,AssociatedID,AttributeID,LandingPage,jsonT,ClientIPAddress
9150,2345,2020-09-01T00:00:36Z,1,,859,campaign01.html,{'product': 'string01', 'type': '', 'string011'},192.168.200.2
9151,2356,2020-09-01T00:02:30Z,1,,640,campaign02.html,{'product': 'string02', 'type': '', 'string022'},192.168.200.1

这是我的桌子：

CREATE TABLE [dbo].[activity](
    [UID] [bigint] NOT NULL,
    [customerID] [bigint] NOT NULL,
    [DateT] [datetime] NULL,
    [TypeID] [bigint] NULL,
    [AssociatedID] [float] NULL,
    [AttributeID] [bigint] NULL,
    [LandingPage] [varchar](256) NULL,
    [jsonT] [varchar](1024) NULL,
    [ClientIPAddress] [varchar](64) NULL,
)
GO

这是我的导入声明：

BULK INSERT dbo.activity
FROM 'C:\tmp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

我不断收到一条错误消息，说要截断最后一列（客户端 IP）。我检查了所有值，它们只有 IP。此外，错误并非在所有行上，我一生都看不到一种 IP 格式或另一种 IP 格式之间的任何区别，它们只是 IP！我尝试简单地使 ClientIPAddress 字段更大（只是为了它），但我只是不断收到相同的错误。

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 6, column 9 (ClientIPAddress).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (2) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

任何关于我可以检查什么或我做错了什么的想法都将受到高度赞赏。

谢谢！

我有一些要导出的表。我正在使用这个包（我称之为“CSV”）：https ://www.williamrobertson.net/documents/refcursor-to-csv.shtml

在添加 CASE 语句之前，我能够写入 CSV 文件。我真的希望有办法解决它，因为我的 CASE 语句非常有用。

错误是：

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "SYS.DBMS_SQL", line 2084

ORA-06512: at "C##USER1.CSV", line 38

ORA-06512: at "C##USER1.CSV", line 162

ORA-06512: at line 1

ORA-06512: at "C##USER1.CSV", line 278

ORA-06512: at "C##USER1.CSV", line 278

ORA-06512: at "C##USER1.EXPORT_02_PRODUCED_PURIFIED", line 5

ORA-06512: at "C##USER1.EXPORT_ALL_LYS_FOR_TABLEAU", line 5

这是错误指向的地方：

ORA-06512: at "C##USER1.EXPORT_02_PRODUCED_PURIFIED", line 5 在这儿：

CREATE OR REPLACE VIEW vw_export_02_produced_purified AS SELECT 
    pk_purified_enz_id,
    fk_construct_id as "fk_for_construct",
    fk_expr_system as "expression_system",
    CASE pr.valid 
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END as "Is this growth ok?",
    g_batch,
    p_batch,
    pur.concentration as "Concentration (uM)",
    pur.abts5_pur_checkpoint as "purification checkpoint",
    pur.yield as "Yield (mg/L)",
        CASE 
            WHEN pk_purified_enz_id IS NULL THEN ' ' 
            WHEN pk_purified_enz_id  IS NOT NULL THEN 
            buffer_mol || ' mM, ' || 
            fk_final_buffer_system || ' ' || 
            'pH ' || final_ph || ', ' ||
            final_nacl || ' mM NaCl' ||
            CASE 
                WHEN final_add IS NULL THEN ' ' 
                WHEN final_add IS NOT NULL THEN ', ' || final_add
            END
        END AS "Purification Buffer",
    buffer_mol,
    fk_final_buffer_system as "buffer system",
    final_ph,
    final_nacl,
    final_add as "other buffer additives" 
    
FROM produced pr FULL OUTER JOIN purified_enz pur
on pr.pk_produced_id = pur.fk_produced_id;


CREATE OR REPLACE PROCEDURE EXPORT_02_PRODUCED_PURIFIED AS 
    l_dataset sys_refcursor;
begin
    open l_dataset for select * from vw_export_02_produced_purified;
    csv.write_file
    ( p_dataset => l_dataset
    , p_heading => 'Y'
    , p_directory => 'EXTRACT_DIR'
    , p_filename => '02_produced_purified.txt' );
END EXPORT_02_PRODUCED_PURIFIED;
/

这个ORA-06512: at "C##USER1.EXPORT_ALL_LYS_FOR_TABLEAU", line 5来自主程序，它结合了所有较小的程序。

CREATE OR REPLACE PROCEDURE EXPORT_ALL_LYS_FOR_TABLEAU AS
BEGIN
EXPORT_00_CPP;
EXPORT_01_CONSTRUCT;
EXPORT_02_PRODUCED_PURIFIED;
EXPORT_03_CHAR;
EXPORT_04_CELL_DATA;
END EXPORT_ALL_LYS_FOR_TABLEAU;
/

被引用的表的 DDL：

CREATE TABLE PRODUCED (
  pk_produced_id NUMBER GENERATED ALWAYS AS IDENTITY,
  fk_construct_id NUMBER NOT NULL,
  g_batch VARCHAR2 (50) NOT NULL, 
  full_g_batch VARCHAR2 (200) GENERATED ALWAYS AS (g_batch || '-' || fk_construct_id) VIRTUAL,
  valid NUMBER (1) DEFAULT 1,
  fk_expr_system VARCHAR2 (50),

  CONSTRAINT pk_produced_id PRIMARY KEY (pk_produced_id),
  CONSTRAINT fk_p_construct_id FOREIGN KEY (fk_construct_id) REFERENCES construct (pk_construct_id),
  CONSTRAINT fk_expr_system FOREIGN KEY (fk_expr_system) REFERENCES EXPR_SYSTEM(pk_expr_system),
  CONSTRAINT uq_produced UNIQUE (fk_construct_id, g_batch) 
);

CREATE TABLE PURIFIED_ENZ (
  pk_purified_enz_id NUMBER GENERATED ALWAYS AS IDENTITY,
  fk_produced_id NUMBER, 
  p_batch VARCHAR2 (50) NOT NULL,
  pur_profile BLOB,
  fk_final_buffer_system VARCHAR2 (50),
  buffer_mol NUMBER (6, 2),
  final_nacl NUMBER (4),
  final_ph NUMBER (4, 2),
  final_add VARCHAR2 (50),
  yield NUMBER (6, 2),
  concentration NUMBER (6, 2),
  abts5_pur_checkpoint NUMBER (5, 2),
  
  CONSTRAINT pk_purified_enz_id PRIMARY KEY (pk_purified_enz_id),
  CONSTRAINT fk_produced_id FOREIGN KEY (fk_produced_id) REFERENCES produced (pk_produced_id),
  CONSTRAINT fk_final_buffer_system FOREIGN KEY (fk_final_buffer_system) REFERENCES buffer_system (pk_final_buffer_system),
  CONSTRAINT uq_purified_enz UNIQUE (fk_produced_id, p_batch)
  );
  

我有一个csv包含 350,000 行的文件。想以与csv文件中相同的顺序将数据插入临时表。我正在尝试BULK INSERT使用：

BULK INSERT ##AllRows
    FROM @FilePath
    WITH
    (
    FIELDTERMINATOR = '\n'
    )

不幸的是BULK INSERT，以随机顺序插入数据。我每次执行的标题都在不同的行中。我在 SQL Server 2016 上运行它。是否有可能在旧版本的 SQL Server 中顺序方式不同？

使用该FIRSTROW选项不会将标题识别为文件的第一行。文件没有我们可以订购的任何列。在文件中，标题总是在第一行。

这可能是巧合，但即使FIRSTROW=2我的标题有可能出现在表格中。我检查了它。看起来csv文件中包含的行越多，插入到表中的可能性就越大。

我有一个用 file_fdw 定义的外部表，它从 CSV 文件中读取数据。Postgresql 解析 CSV 文件并将日期例如从转换YYYYMMDD为YYYY-MM-DD. 这工作正常。

我的问题是解析 CSV 文件的频率如何？它会被解析select吗？还是被缓存了？如果是这样，它会被缓存多长时间？

我正在尝试将下面列出的查询结果导出到 csv 并在第一行中包含列名。但是，当我运行代码并打开 csv 时，标题被列为最后一行。我不知道为什么。我错过了什么？

SELECT 'id','state','First_Name', 'Last_Name', 'Phone','County'
UNION ALL
SELECT id, state, First_Name, Last_Name, Phone, County
FROM tx.students
GROUP BY First_Name, Last_Name, Phone,
HAVING COUNT(*)>1
ORDER BY id
INTO OUTFILE '/var/lib/mysql-files/_students_dupes_test3.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

有没有办法使用 sql 函数或已经存在的 psql 函数将一个非常大的表复制到多个 csv 文件中并指定输出文件中允许的最大行数？

例如，一个 3500 万行的表将输出 35 个 100 万行的 csv 文件。

我是 pgAdmin 和 Azure 数据库的新手。我有一个包含大约 220 列的巨大 csv 文件，我想在 pgAdmin4 中创建一个表以将其推送到 Azure。

但是我不知道如何自动检测列的类型。

这是一个例子：

IDI GKID    S01Q01  S02Q01_Gender ...
100093  enq030059569748fc89091fdd91cc337cac44eca90  Yes, I agree    Female ...

我正在编写脚本来创建表格。但是，考虑到列的数量，我想自动化它以获取允许我将 csv 添加到 Microsoft Azure 的 pgAdmin4 中的数据库的脚本。

然后，在转置 csv 标头后，我得到：

    IDI
    GKID
    S01Q01
    S02Q01_Gender
    ...

我正在编写脚本来创建表格。但是，考虑到列的数量，在最好的情况下，我希望自动检测列的类型以便能够将其写入正确的部分，在最坏的情况下，放置 TEXT 类型的泛型类型。

到目前为止，我已经尝试过

output = ""
file_name = "columns.txt"
string_to_add = " TINYTEXT,"

with open(file_name, 'r') as f:
    file_lines = [''.join([x.strip(), string_to_add, '\n']) for x in f.readlines()]

with open(file_name, 'w') as f:
    f.writelines(file_lines) 

它给了我：

IDI TINYTEXT,
GKID TINYTEXT,
S01Q01 TINYTEXT,
S02Q01_Gender TINYTEXT,
...

然后，我可以这样做：

CREATE TABLE my_table (
IDI TINYTEXT,
GKID TINYTEXT,
S01Q01 TINYTEXT,
S02Q01_Gender TINYTEXT,
...

但我不确定这是否足以使表格能够接收我的 csv 文件。