mysql - 在 infobright 中加载数据 infile csv 文件

Question

我有一个表(在infobright中成功创建)。

我使用的是windows系统

CREATE TABLE `file_records` (
  `id` int(11) NOT NULL ,
  `file_id` int(11)  NULL,
  `file_url` varchar(255)  NULL,
  `switch_id` int(11)  NULL,
  `carrierid_supplier` int(11)  NULL,
  `technical_profileid_supplier` int(11)  NULL,
  `carrierid_customer` int(11)  NULL,
  `technical_profileid_customer` int(11)  NULL,
  `billing_increment_supplier` varchar(10)  NULL,
  `billing_increment_customer` varchar(10)  NULL,
  `billable_duration_supplier` int(11)  NULL,
  `call_duration_seconds` int(11)  NULL,
  `billable_duration_customer` int(11)  NULL,
  `destination` varchar(50)  NULL,
  `destination_country` varchar(50)  NULL,
  `destination_country_number` varchar(50)  NULL,
  `rateplanid_supplier` int(11)  NULL,
  `rateplanid_customer` int(11)  NULL,
  `rate_supplier` int(11)  NULL,
  `rate_customer` int(11)  NULL,
  `rate_total_supplier` varchar(10)  NULL,
  `rate_total_customer` varchar(10)  NULL,
  `rate_effective_date_supplier` date  NULL,
  `rate_effective_date_customer` date  NULL,
  `call_hour` varchar(10)  NULL,
  `sequence_number` int(11)  NULL,
  `version` varchar(10)  NULL,
  `record_type` varchar(1)  NULL,
  `connection_type` varchar(16)  NULL,
  `session_id` varchar(36)  NULL,
  `release_cause` smallint(6)  NULL,
  `start_time_date` datetime  NULL,
  `answer_time_date` datetime  NULL,
  `release_tod` datetime  NULL,
  `greenwich_mean_time` varchar(32)  NULL,
  `release_cause_protocol_stack` varchar(32)  NULL,
  `binary_value_protocol_stack` smallint(6)  NULL,
  `first_release_dialogue` varchar(1)  NULL,
  `origination_trunkid` int(11)  NULL,
  `voip_protocol` varchar(6)  NULL,
  `source_number` varchar(128)  NULL,
  `source_host_name` varchar(128)  NULL,
  `destination_number` varchar(128)  NULL,
  `destination_host_name` varchar(128)  NULL,
  `callid` varchar(128)  NULL,
  `remote_payload_ipaddress` varchar(16)  NULL,
  `remote_payload_udpaddress` varchar(6)  NULL,
  `local_payload_ipaddress` varchar(16)  NULL,
  `local_payload_udpaddress` varchar(6)  NULL,
  `codec_list` varchar(128)  NULL,
  `ingress_packets` int(11)  NULL,
  `egress_packets` int(11)  NULL,
  `ingress_octets` int(11)  NULL,
  `egress_octets` int(11)  NULL,
  `ingress_packet_loss` int(11)  NULL,
  `ingress_delay` int(11)  NULL,
  `ingress_packet_jitter` int(11)  NULL,
  `supplierid` mediumint(9)  NULL,
  `protocol` varchar(6)  NULL,
  `termination_source_number` varchar(128)  NULL,
  `termination_source_host` varchar(128)  NULL,
  `termination_destination_number` varchar(128)  NULL,
  `termination_destination_host_name` varchar(128)  NULL,
  `termination_callid` varchar(128)  NULL,
  `termination_remote_payload_ipaddress` varchar(16)  NULL,
  `termination_remote_payload_udpaddress` varchar(6)  NULL,
  `termination_local_payload_ipaddress` varchar(16)  NULL,
  `termination_local_payload_udpaddress` varchar(6)  NULL,
  `termination_codec_list` varchar(128)  NULL,
  `termination_ingress_packets` int(11)  NULL,
  `termination_egress_packets` int(11)  NULL,
  `termination_ingress_octets` int(11)  NULL,
  `termination_egress_octets` int(11)  NULL,
  `termination_ingress_packet_loss` int(11)  NULL,
  `termination_ingress_delay` int(11)  NULL,
  `termination_ingress_packet_jitter` int(11)  NULL,
  `final_route_indication` varchar(1)  NULL,
  `routing_digits` varchar(64)  NULL,
  `call_duration` mediumint(9)  NULL,
  `post_dial_delay` mediumint(9)  NULL,
  `ring_time` mediumint(9)  NULL,
  `call_duration_ms` int(11)  NULL,
  `confid` varchar(32)  NULL,
  `rpid` varchar(32)  NULL,
  `route_entry_index` tinyint(4)  NULL,
  `route_table_used` mediumint(9)  NULL,
  `lnp_dipped` varchar(1)  NULL,
  `ingress_lrn` varchar(32)  NULL,
  `egress_lrn` varchar(32)  NULL,
  `cnam_dipped` tinyint(4)  NULL,
  `dnc_dipped` tinyint(4)  NULL,
  `origination_device_name` varchar(15)  NULL,
  `termination_device_name` varchar(15)  NULL,
  `ers_dipped` varchar(1)  NULL,
  `oli_digits` varchar(8)  NULL
) ENGINE=Brighthouse;

并使用加载csv数据文件

LOAD DATA INFILE 'C:/Users/dhairya/Desktop/a.csv' INTO TABLE file_records FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' ( sequence_number, version, record_type, connection_type, session_id, release_cause, @start_time_date, @answer_time_date, @release_tod, greenwich_mean_time, release_cause_protocol_stack, binary_value_protocol_stack, first_release_dialogue, origination_trunkid, voip_protocol, source_number, source_host_name, destination_number, destination_host_name, callid, remote_payload_ipaddress, remote_payload_udpaddress, local_payload_ipaddress, local_payload_udpaddress, codec_list, ingress_packets, egress_packets, ingress_octets, egress_octets, ingress_packet_loss, ingress_delay, ingress_packet_jitter, supplierid, protocol, termination_source_number, termination_source_host, termination_destination_number, termination_destination_host_name, termination_callid, termination_remote_payload_ipaddress, termination_remote_payload_udpaddress, termination_local_payload_ipaddress, termination_local_payload_udpaddress, termination_codec_list, termination_ingress_packets, termination_egress_packets, termination_ingress_octets, termination_egress_octets, termination_ingress_packet_loss, termination_ingress_delay, termination_ingress_packet_jitter, final_route_indication, routing_digits, call_duration, post_dial_delay, ring_time, call_duration_ms, confid, rpid, route_entry_index, route_table_used, lnp_dipped, ingress_lrn, egress_lrn, cnam_dipped, dnc_dipped, origination_device_name, termination_device_name, ers_dipped, oli_digits, routing_match, termination_pdd, proxy_charge_information, jurisdictoinal_indication_parameter, matched_digits_ers, route_table_irs, route_sequence_number_ers, jurisdiction_type_ers) set start_time_date = 0, answer_time_date = 0, release_tod = 0, file_id = 1;

我总是收到 err 2 Hy0000 :错误的数据或列定义

示例 csv 数据如下

018002724;V1.17;R;No-Media;316773032-878328180@98.122.122.122;0001;Sat Apr 27 23:57:21 2013;Sat Apr 27 23:57:54 2013;Sat Apr 27 23:58:44 2013;0;Normal BYE; 200;O;030057;SIP;0186645212;80.84.30.12;6282330238419;98.123.123.000;7006204640450523785-1367107040-32218;80.84.30.12;39264;NA;NA;G.729,G.723.1,RFC 2833,G.711a_64k,G.711u_64k;0;0;0;0;0;0;0;090069;SIP;0186645212;98.122.122.122;6282330238419;112.123.123.123;316773032-0-878328180@98.158.145.166;123.123.123.2;24992;NA;NA;G.729,RFC 2833;0;0;0;0;0;0;0;F;6282330238419;49;1;32;49680;12E192A8 345A3974 ADF7A11C A6919E62;;1;103;0;;;0;0;SP Belgacom - B;Vinculum WHS Ve;0;;62823;1140;;;;0;0;0;

我检查 csv 文件的终止符为“\n”

上面的查询在 mysql 中可以正常工作

Answer 1

问题是 BRIGHTHOUSE 加载程序不支持在 LOAD DATA INFILE 中指定列命令。它也不支持在此命令中设置列的默认值。

要像这样正确使用 BRIGHTHOUSE 表，您有 4 个选项:

• 使表架构与 CSV 文件布局匹配，
• 使 CSV 布局与表格架构匹配，
• 使用设置 SET @BH_DATAFORMAT = 'mysql'使用标准 mysql 加载器而不是 BRIGHTHOUSE 加载器，
• 或者创建一个与您的 CSV 布局相匹配的新临时表，加载到其中，然后将该表的内容写入新的 CSV 文件，其格式与原始表所需的 CSV 格式相匹配。

我知道这很痛苦，过去 6 个月我一直在使用 Infobright 数据库，使用起来真的很头疼。

此外，在使用 BRIGHTHOUSE 加载程序时不要忘记这些方便的设置:

• SET @BH_DATAFORMAT = 'txt_variable';这意味着使用 BRIGHTHOUSE 加载程序。
• SET @BH_DATAFORMAT = 'mysql';这意味着使用 MySQL 加载程序。
• SET @BH_REJECT_FILE_PATH = '/tmp/new_file_name';这意味着当 CSV 文件中的任何行与您要加载的架构不匹配时，请将其复制到此输出文件中。这对于调试特别有帮助，或者当您有大型 CSV 文件且无法删除 header 时，因为 BRIGHTHOUSE 加载程序不实现 IGNORE n LINES LOAD DATA INFILE 中的参数命令。
• SET @BH_ABORT_ON_COUNT = 1000;当输入 CSV 文件中的此行数与要加载到的表的架构不匹配时，中止文件加载命令。

另请记住，BRIGHTHOUSE CSV 导出器中存在一个错误，这意味着生成的 CSV 文件中不会转义反斜杠。因此，如果您想将此 CSV 加载回数据库架构中，您将需要使用 LOAD DATA INFILE '...' INTO TABLE ... FIELDS ESCAPED BY ''或者导入器将使用反斜杠作为转义序列的一部分来更改其后面的字符的含义。

这里有一个方便的 SQL block ，可以帮助您将 CSV 加载到架构不匹配的 BRIGHTHOUSE 表中:

CREATE TABLE `tmptable` ENGINE=MySQL SELECT * FROM `maintable` WHERE 0 LIMIT 1;

@SET BH_DATAFORMAT = 'mysql';

LOAD DATA INFILE '/tmp/myfile' INTO TABLE `tmptable`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(field1, field2, field3) SET field4 = 'somevalue'; /*as per your CSV layout*/

SELECT * FROM tmptable INTO OUTFILE '/tmp/mynewfile.csv' 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '' 
LINES TERMINATED BY '\n';

DROP TABLE `tmptable`;

@SET BH_DATAFORMAT = 'txt_variable';
@SET BH_REJECT_FILE_PATH = '/tmp/failed_csv_lines.csv';
@SET BH_ABORT_ON_COUNT = 10;

LOAD DATA INFILE '/tmp/mynewfile.csv' INTO TABLE `maintable`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '' 
LINES TERMINATED BY '\n';

(WHERE 0 LIMIT 1 位可以工作，而 LIMIT 0 不能工作 - 在 MySQL 中 LIMIT 0 工作正常，但是当使用 BRIGHTHOUSE 时，它会抛出一些非常奇怪的错误，我花了一段时间才找到，所以我使用而是使用此方法。)

我知道这个答案有点晚了，但我希望它对任何偶然发现这个问题的人都有用。