I have a CSV file with 300 million rows that I need to import in a MariaDB Columnstore database.
I use this statement:
LOAD DATA INFILE '/data/fnma_loans.csv' into table fnma_loans fields terminated by '|' LINES TERMINATED BY '\n';
But I get an error:
ERROR 1366 (22007): Incorrect integer value: '' for column
db_0001.fnma_loans.co_borrower_credit_score_at_originationat row 1
The issue is that the column value is empty in the file, but the column default is NULL, so I don't understand why this is failing as it should insert NULL if there's no value in the file.
This is the table, how to tell MariaDb/MySql that it should insert null in the row?
CREATE TABLE `fnma_loans` (
`reference_pool_id` char(4) DEFAULT NULL,
`loan_identifier` char(12) DEFAULT NULL,
`monthly_reporting_period_char` char(6) DEFAULT NULL,
`channel` varchar(1) DEFAULT NULL,
`seller_name` varchar(50) DEFAULT NULL,
`servicer_name` varchar(50) DEFAULT NULL,
`master_servicer` varchar(10) DEFAULT NULL,
`original_interest_rate` decimal(7,3) DEFAULT NULL,
`current_interest_rate` decimal(7,3) DEFAULT NULL,
`original_upb` decimal(13,2) DEFAULT NULL,
`upb_at_issuance` decimal(13,2) DEFAULT NULL,
`current_actual_upb` decimal(13,2) DEFAULT NULL,
`original_loan_term` smallint(6) DEFAULT NULL,
`origination_date` char(6) DEFAULT NULL,
`first_payment_date` char(6) DEFAULT NULL,
`loan_age` smallint(6) DEFAULT NULL,
`remaining_months_to_legal_maturity` smallint(6) DEFAULT NULL,
`remaining_months_to_maturity` smallint(6) DEFAULT NULL,
`maturity_date` char(6) DEFAULT NULL,
`original_loan_to_value_ratio` smallint(6) DEFAULT NULL,
`original_combined_loan_to_value_ratio` smallint(6) DEFAULT NULL,
`number_of_borrowers` smallint(6) DEFAULT NULL,
`debt_to_income` smallint(6) DEFAULT NULL,
`borrower_credit_score_at_origination` smallint(6) DEFAULT NULL,
`co_borrower_credit_score_at_origination` smallint(6) DEFAULT NULL, // this is the column that fails
`first_time_home_buyer_indicator` varchar(1) DEFAULT NULL,
`loan_purpose` varchar(1) DEFAULT NULL,
`property_type` varchar(2) DEFAULT NULL,
`number_of_units` smallint(6) DEFAULT NULL,
`occupancy_status` varchar(1) DEFAULT NULL,
`property_state` varchar(2) DEFAULT NULL,
`metropolitan_statistical_area` varchar(5) DEFAULT NULL,
`zip_code_short` varchar(3) DEFAULT NULL,
`mortgage_insurance_percentage` decimal(7,2) DEFAULT NULL,
`amortization_type` varchar(3) DEFAULT NULL,
`prepayment_penalty_indicator` varchar(1) DEFAULT NULL,
`interest_only_loan_indicator` varchar(1) DEFAULT NULL,
`interest_only_first_principal_and_interest_payment_date` char(6) DEFAULT NULL,
`months_to_amortization` smallint(6) DEFAULT NULL,
`current_loan_delinquency_status` char(2) DEFAULT NULL,
`loan_payment_history` varchar(48) DEFAULT NULL,
`modification_flag` varchar(1) DEFAULT NULL,
`mortgage_insurance_cancellation_indicator` varchar(2) DEFAULT NULL,
`zero_balance_code` varchar(3) DEFAULT NULL,
`zero_balance_effective_date` char(6) DEFAULT NULL,
`upb_at_the_time_of_removal` decimal(13,2) DEFAULT NULL,
`repurchase_date` char(6) DEFAULT NULL,
`scheduled_principal_current` decimal(13,2) DEFAULT NULL,
`total_principal_current` decimal(13,2) DEFAULT NULL,
`unscheduled_principal_current` decimal(13,2) DEFAULT NULL,
`last_paid_installment_date` char(6) DEFAULT NULL,
`foreclosure_date` char(6) DEFAULT NULL,
`disposition_date` char(6) DEFAULT NULL,
`foreclosure_costs` decimal(13,2) DEFAULT NULL,
`property_preservation_and_repair_costs` decimal(13,2) DEFAULT NULL,
`asset_recovery_costs` decimal(13,2) DEFAULT NULL,
`miscellaneous_holding_expenses_and_credits` decimal(13,2) DEFAULT NULL,
`associated_taxes_for_holding_property` decimal(13,2) DEFAULT NULL,
`net_sales_proceeds` decimal(13,2) DEFAULT NULL,
`credit_enhancement_proceeds` decimal(13,2) DEFAULT NULL,
`repurchase_make_whole_proceeds` decimal(13,2) DEFAULT NULL,
`other_foreclosure_proceeds` decimal(13,2) DEFAULT NULL,
`non_interest_bearing_upb` decimal(13,2) DEFAULT NULL,
`principal_forgiveness_amount` decimal(13,2) DEFAULT NULL,
`original_list_start_date` char(6) DEFAULT NULL,
`original_list_price` decimal(13,2) DEFAULT NULL,
`current_list_start_date` char(6) DEFAULT NULL,
`current_list_price` decimal(13,2) DEFAULT NULL,
`borrower_credit_score_at_issuance` int(11) DEFAULT NULL,
`co_borrower_credit_score_at_issuance` int(11) DEFAULT NULL,
`borrower_credit_score_current` int(11) DEFAULT NULL,
`co_borrower_credit_score_current` int(11) DEFAULT NULL,
`mortgage_insurance_type` char(1) DEFAULT NULL,
`servicing_activity_indicator` char(1) DEFAULT NULL,
`current_period_modification_loss_amount` decimal(13,2) DEFAULT NULL,
`cumulative_modification_loss_amount` decimal(13,2) DEFAULT NULL,
`current_period_credit_event_net_gain_or_loss` decimal(13,2) DEFAULT NULL,
`cumulative_credit_event_net_gain_or_loss` decimal(13,2) DEFAULT NULL,
`homeready_program_indicator` char(1) DEFAULT NULL,
`foreclosure_principal_write_off_amount` decimal(13,2) DEFAULT NULL,
`relocation_mortgage_indicator` char(1) DEFAULT NULL,
`zero_balance_code_change_date` char(6) DEFAULT NULL,
`loan_holdback_indicator` char(1) DEFAULT NULL,
`loan_holdback_effective_date` char(6) DEFAULT NULL,
`delinquent_accrued_interest` decimal(13,2) DEFAULT NULL,
`property_valuation_method` char(1) DEFAULT NULL,
`high_balance_loan_indicator` char(1) DEFAULT NULL,
`arm_initial_fixed_rate_period_5_yr_indicator` char(1) DEFAULT NULL,
`arm_product_type` varchar(100) DEFAULT NULL,
`initial_fixed_rate_period` smallint(6) DEFAULT NULL,
`interest_rate_adjustment_frequency` smallint(6) DEFAULT NULL,
`next_interest_rate_adjustment_date` char(6) DEFAULT NULL,
`next_payment_change_date` char(6) DEFAULT NULL,
`index_text` varchar(100) DEFAULT NULL,
`arm_cap_structure` varchar(10) DEFAULT NULL,
`initial_interest_rate_cap_up_percent` decimal(7,4) DEFAULT NULL,
`periodic_interest_rate_cap_up_percent` decimal(7,4) DEFAULT NULL,
`lifetime_interest_rate_cap_up_percent` decimal(7,4) DEFAULT NULL,
`mortgage_margin` decimal(7,4) DEFAULT NULL,
`arm_balloon_indicator` char(1) DEFAULT NULL,
`arm_plan_number` smallint(6) DEFAULT NULL,
`borrower_assistance_plan` char(1) DEFAULT NULL,
`high_loan_to_value_refinance_option_indicator` char(1) DEFAULT NULL,
`deal_name` varchar(200) DEFAULT NULL,
`repurchase_make_whole_proceeds_flag` char(1) DEFAULT NULL,
`alternative_delinquency_resolution` char(1) DEFAULT NULL,
`alternative_delinquency_resolution_count` smallint(6) DEFAULT NULL,
`total_deferral_amount` decimal(13,2) DEFAULT NULL,
`monthly_reporting_period` date DEFAULT NULL,
`mat_date` date DEFAULT NULL,
`payment_amount` decimal(9,2) DEFAULT NULL) ;
