Import data from MySQL to the Greenplum cluster

We want to export data from MySQL to Greenplum, follow these steps to

1: Export tables from MySQL to external files

Taking Schema_name.table_name as an example

Selectproduct_id, Number, name, English_name, Purchase_name, System_name, Bar_code, Category_one, Category_two, Category_three, parent_id, Parent_number, brand_id, supplier_id, Price, Ad_word, Give_integral, Shelf_life, FROM_ Unixtime (shelve_date), Product_area, Country, sale_unit, specification, weight, length, width, height, storage_ conditions, storage, model, refuse_notes, status, Is_promote, Is_gift, Is_book, is_outgoing, Is_presale, Is_fragile, Is_h Ave, Is_cod, Is_return, Is_oos, Is_seasonal, is_multicity, Is_package, Is_show, click, Favorite, Min_purchase_unit, IN_PR Ice, Refer_in_price, Mwaverage_price, Is_unique_number, Is_batch_number, Qs_proportion, Shelf_life_proportion, Box_ Specification, max_unsalable, advent_shelves, pro_warning, From_unixtime (Add_time), Operator_id,from_unixtime (audit _time), remark, Price_type, New_tag, Product_type, Business_model, Is_sell, Return_policy, package, inventory, Merchant_ Number, Modified_time, now ()  fromSchema_name.table_name intoOUTFILE'/tmp/table_name.txt';

Guide the time to note that some character conversion, for this table, mainly in MySQL some of the time format is stored in the type of int, we need to convert and then export, and in the Greenplum of the table in the time will be one more time field, we are here by default export time. Export in the format above.

2: Copy the file to the Greenplum server and create the external table

Copy the file to the directory of the external table first, this is simple, what method can be, and then create an external table:

CreateExternalTABLESchema_name.table_name_ext (product_idint, Number varchar(Ten), namevarchar( -), English_namevarchar( -), Purchase_namevarchar( -), System_namevarchar( -), Bar_codevarchar(255), Category_oneint, Category_twoint, Category_threeint, parent_idint, Parent_numberint, brand_idint, supplier_idint, Priceint, Ad_wordvarchar( -), Give_integralint, Shelf_lifeint, Shelve_datetimestampwithout time Zone,product_areaint, Countryint, Sale_unitvarchar( -), Specificationvarchar(255), Weightdecimal(Ten,2), Lengthint, Widthint, Heightint, Storage_conditionsvarchar(255), Storagesmallint, Modelvarchar( -), Refuse_notesvarchar(255), Statussmallint, Is_promotesmallint, Is_giftsmallint, Is_booksmallint, Is_outgoingsmallint, Is_presaleint, Is_fragilesmallint, Is_havesmallint, Is_codsmallint, Is_returnsmallint, Is_oossmallint, Is_seasonalsmallint, Is_multicitysmallint, Is_packagesmallint, Is_showsmallint, clickint, Favoriteint, Min_purchase_unitint, In_priceint, Refer_in_priceint, Mwaverage_priceint, Is_unique_numberint, Is_batch_numberint, Qs_proportionint, Shelf_life_proportionDOUBLE PRECISION, Box_specificationvarchar( -), max_unsalableint, Advent_shelvesint, Pro_warningint, Add_timetimestampwithout time zone,operator_idint, Audit_timetimestampwithout time Zone,remarkvarchar(255), Price_typesmallint, New_tagint, Product_typeint, Business_modelsmallint, Is_sellsmallint, Return_policysmallint, the packagevarchar( $), Inventoryvarchar( $), Merchant_numberint, Modified_timetimestampwithout time Zone,dw_modified_timetimestampwithout time zone) location ('gpfdist://172.16.16.34:9888/Table_name.txt' )
FORMAT'TEXT'SEGMENT REJECT LIMIT1000000rows;

Here we want to specify ' Gpfdist://10.102.35.192:9888/table_name.txt ', this IP address with external table on it, after the file to be copied to the Gpfdist directory, we look at the starting mode gpfdist-d /tmp-p 9888, that is, to copy the external files to the/tmp directory. The other Attention column name correspondence is good

Then query, the general situation on the list will not be a problem.

3: Import into Greenplum official table

Create a formal table first:

Create TableSchema_name.table_name (product_idint, Number varchar(Ten), namevarchar( -), English_namevarchar( -), Purchase_namevarchar( -), System_namevarchar( -), Bar_codevarchar(255), Category_oneint, Category_twoint, Category_threeint, parent_idint, Parent_numberint, brand_idint, supplier_idint, Priceint, Ad_wordvarchar( -), Give_integralint, Shelf_lifeint, Shelve_datetimestampwithout time Zone,product_areaint, Countryint, Sale_unitvarchar( -), Specificationvarchar(255), Weightdecimal(Ten,2), Lengthint, Widthint, Heightint, Storage_conditionsvarchar(255), Storagesmallint, Modelvarchar( -), Refuse_notesvarchar(255), Statussmallint, Is_promotesmallint, Is_giftsmallint, Is_booksmallint, Is_outgoingsmallint, Is_presaleint, Is_fragilesmallint, Is_havesmallint, Is_codsmallint, Is_returnsmallint, Is_oossmallint, Is_seasonalsmallint, Is_multicitysmallint, Is_packagesmallint, Is_showsmallint, clickint, Favoriteint, Min_purchase_unitint, In_priceint, Refer_in_priceint, Mwaverage_priceint, Is_unique_numberint, Is_batch_numberint, Qs_proportionint, Shelf_life_proportionDOUBLE PRECISION, Box_specificationvarchar( -), max_unsalableint, Advent_shelvesint, Pro_warningint, Add_timetimestampwithout time zone,operator_idint, Audit_timetimestampwithout time Zone,remarkvarchar(255), Price_typesmallint, New_tagint, Product_typeint, Business_modelsmallint, Is_sellsmallint, Return_policysmallint, the packagevarchar( $), Inventoryvarchar( $), Merchant_numberint, Modified_timetimestampwithout time Zone,dw_modified_timetimestampwithout time zone)Distributed  by(product_id);

Then import the data:

Insertschema_name.table_name

Select *  Schema_name.table_name_ext

This will export the external table data to the internal table, evenly distributed on each segment. Note that the structure of the schema_name.table_name is consistent with the schema_name.table_name_ext.

Import data from MySQL to the Greenplum cluster