在infobright中加载数据填充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;

I总是得到错误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;

我将终止检查为'\n'，用于csv文件

上面的查询在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文件时特别有帮助，因为BRIGHTHOUSE加载器没有在LOAD DATA INFILE命令中实现LOAD DATA INFILE参数。
• 当输入CSV文件中的这几行与要加载到的表的架构不匹配时，SET @BH_ABORT_ON_COUNT = 1000;将使用文件加载命令。

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

下面是一个方便的SQL块，它可以帮助您将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时会抛出一些非常奇怪的错误，我花了很长时间才找到它，所以我使用了这个方法。)

我知道这个答案来得有点晚，但我希望它对任何遇到这个问题的人都有帮助。