主頁 >  其他 > 客快物流大資料專案(二十五)：初始化業務資料

客快物流大資料專案(二十五)：初始化業務資料

2022-02-03 07:20:38 其他

目錄

初始化業務資料

一、安裝OGG源端

1、在Oracle中創建OGG相關的用戶和表空間

2、OGG源端初始化

二、配置OGG源端

1、Oracle創建物流相關表

2、配置管理器MGR行程

3、???????配置extract行程

4、配置pump行程

5、???????配置define檔案

三、配置OGG目標端

1、???????拷貝源端的define檔案到目標端

2、???????配置管理器MRG行程

3、配置Replicate行程

4、???????配置kafka.props

???????5、最后確認所有的行程

四、???????OGG測驗

1、???????啟動kafka消費者

2、???????archivelog日志路徑

初始化業務資料

一、安裝OGG源端

1、在Oracle中創建OGG相關的用戶和表空間

操作步驟

說明

1

創建表空間在磁盤中的物理路徑（需要到root用戶操作）

mkdir -p /u01/app/oracle/oggdata/orcl/

chown -R oracle:oinstall /u01/app/oracle/oggdata/orcl

2

進入sqlplus

切換到oracle用戶：su - oracle

登錄sqlplus：sqlplus "/as sysdba"

3

創建oggtbs表空間

CREATE TABLESPACE "TBS_LOGISTICS" DATAFILE '/u01/app/oracle/oradata/orcl/tbs_logistics.dat' SIZE 500M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

4

創建ogg用戶(用戶名和密碼都是ogg)

CREATE USER ogg IDENTIFIED BY ogg DEFAULT TABLESPACE TBS_LOGISTICS;

5

賦予ogg用戶dba權限

GRANT connect,resource,dba to ogg;

2、OGG源端初始化

操作步驟

說明

1

使用oracle用戶登錄源端OGG的命令列中

su – oracle

cd $OGG_SRC_HOME

./ggsci

2

初始化源端OGG目錄

注意：如果不在OGG_SRC_HOME下，初始化OGG目錄時會報錯

create subdirs

退出OGG命令列客戶端：exit

3

檢查源端OGG初始化后的目錄

初始化完成后，可以查詢在$OGG_SRC_HOME下是否存在dirchk、dirdat、dirdef、dirjar、dirout、dirpcs、dirprm、dirrpt、dirsql、dirtmp共11個目錄，

二、???????配置OGG源端

1、???????Oracle創建物流相關表

屬性名

屬性值

主機地址

192.168.88.10

埠號

1521

資料庫實體名稱

ORCL

資料庫實體型別

Service Name

用戶名

ogg

角色

Normal

密碼

ogg

JDBC訪問URL

jdbc:oracle:thin:@//192.168.88.10:1521:ORCL

JDBC驅動名稱

使用OracleDataSource連接池，無需配置Driver

業務SQL

-- 創建所有的序列
CREATE SEQUENCE tbl_emp_info_map_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_driver_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_emp_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_warehouse_tt_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_charge_standard_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_company_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_company_dot_map_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_company_route_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_company_warehouse_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_courier_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_deliver_region_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_delivery_record_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_department_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_fixed_area_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_goods_rack_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_job_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_out_warehouse_dtl_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_pkg_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_postal_standard_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_push_warehouse_dtl_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_service_evaluation_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_store_grid_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_vehicle_monitor_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_warehouse_rack_map_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_warehouse_receipt_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_waybill_line_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_waybill_record_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_work_time_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_test_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_areas_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_deliver_package_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_customer_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_codes_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_warehouse_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_consumer_address_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_warehouse_receipt_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_warehouse_send_vehicle_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_warehouse_vehicle_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_dot_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_transport_tool_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_dot_transport_tool_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_address_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_route_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_push_warehouse_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_out_warehouse_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_warehouse_emp_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_express_package_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_express_bill_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_consumer_sender_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_collect_package_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;
CREATE SEQUENCE tbl_waybill_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999999999;

-- 創建所有的業務表
CREATE TABLE "tbl_emp_info_map" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"company_id" NUMBER(19,0), 
"dot_id" NUMBER(19,0), 
"emp_id" NUMBER(19,0), 
"job_id" NUMBER(19,0), 
"dep_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
CONSTRAINT "PK_TBL_EMP_INFO_MAP" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS";

CREATE TABLE "tbl_driver" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"job_number" NVARCHAR2(50), 
"name" NVARCHAR2(50), 
"gender" NVARCHAR2(100),
"birathday" DATE, 
"state" NUMBER(19,0), 
"driver_license_number" NVARCHAR2(100), 
"driver_license_type" NUMBER(19,0), 
"get_driver_license_dt" DATE, 
"car_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_DRIVER" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_emp" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"emp_number" NVARCHAR2(50), 
"emp_name" NVARCHAR2(50), 
"emp_gender" NUMBER(10,0), 
"emp_birathday" DATE, 
"state" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_EMP" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_warehouse_transport_tool" (  
"id" NUMBER(19,0) NOT NULL ENABLE, 
"warehouse_id" NUMBER(19,0), 
"transport_tool_id" NUMBER(19,0), 
"allocate_dt" DATE, 
"state" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_WAREHOUSE_TRANSPORT_TOOL" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_charge_standard" (   
"id" NUMBER(19,0) NOT NULL ENABLE, 
"start_area_id" NUMBER(19,0), 
"stop_area_id" NUMBER(19,0), 
"first_weight_charge" NUMBER(19,0), 
"follow_up_weight_charge" NUMBER(19,0), 
"prescription" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_CHARGE_STANDARD" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS";

CREATE TABLE "tbl_company" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"company_name" NVARCHAR2(50), 
"city_id" NUMBER(19,0), 
"company_number" NVARCHAR2(50), 
"company_addr" NVARCHAR2(100), 
"company_addr_gis" NVARCHAR2(100), 
"company_tel" NVARCHAR2(20), 
"is_sub_company" NUMBER(19,0), 
"state" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_COMPANY" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS";

CREATE TABLE "tbl_company_dot_map" (   
"id" NUMBER(19,0) NOT NULL ENABLE, 
"company_id" NUMBER(19,0), 
"dot_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_COMPANY_DOT_MAP" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_company_transport_route_ma" 
(
"id" NUMBER(19,0) NOT NULL ENABLE, 
"company_id" NUMBER(19,0), 
"transport_route_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_COMPANY_TRANSPORT_ROUTE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_company_warehouse_map" ( 
"id" NUMBER(19,0) NOT NULL ENABLE, 
"company_id" NUMBER(19,0), 
"warehouse_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_COMPANY_WAREHOUSE_MAP" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_courier" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"job_num" NVARCHAR2(50), 
"name" NVARCHAR2(50), 
"birathday" DATE, 
"tel" NVARCHAR2(20), 
"pda_num" NVARCHAR2(50), 
"car_id" NUMBER(19,0), 
"postal_standard_id" NUMBER(19,0), 
"work_time_id" NUMBER(19,0), 
"dot_id" NUMBER(19,0), 
"state" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_COURIER" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_deliver_region" 
(
"id" NUMBER(19,0) NOT NULL ENABLE, 
"search_keyword" NVARCHAR2(100), 
"search_assist_keyword" NVARCHAR2(100), 
"area_id" NUMBER(19,0), 
"fixed_area_id" NUMBER(19,0), 
"state" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_DELIVER_REGION" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_delivery_record" (   
"id" NUMBER(19,0) NOT NULL ENABLE, 
"cur_warehouse_id" NVARCHAR2(50), 
"vehicle_id" NUMBER(19,0), 
"start_vehicle_dt" DATE,
"next_warehouse_id" NUMBER(19,0), 
"predict_arrivals_dt" DATE,
"actua_arrivals_dt" DATE,
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_DELIVERY_RECORD" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_department" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"dep_name" NVARCHAR2(50), 
"dep_level" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_DEPARTMENT" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_fixed_area" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"name" NVARCHAR2(50), 
"emp_id" NUMBER(19,0), 
"operator_dt" DATE, 
"operator_id" NUMBER(19,0), 
"gis_fence" NVARCHAR2(200), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_FIXED_AREA" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_goods_rack" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"warehouse_name" NVARCHAR2(50), 
"warehouse_addr" NVARCHAR2(100),
"warehouse_addr_gis" NVARCHAR2(50), 
"company_id" NUMBER(19,0), 
"employee_id" NVARCHAR2(200), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_GOODS_RACK" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_job" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"job_name" NVARCHAR2(50), 
"job_level" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_JOB" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_out_warehouse_detail" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"push_warehouse_id" NUMBER(19,0), 
"push_warehouse_bill" NVARCHAR2(100), 
"warehouse_id" NUMBER(19,0), 
"waybill_id" NUMBER(19,0), 
"pkg_id" NUMBER(19,0), 
"pkg_desc" NVARCHAR2(100), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_OUT_WAREHOUSE_DETAIL" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_pkg" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"pw_bill" NVARCHAR2(50), 
"pw_dot_id" NUMBER(19,0), 
"warehouse_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_PKG" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_postal_standard" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"name" NVARCHAR2(50), 
"min_weight" NVARCHAR2(50), 
"min_length" NVARCHAR2(50), 
"max_length" NVARCHAR2(50), 
"trajectory" NVARCHAR2(50), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_POSTAL_STANDARD" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_push_warehouse_detail" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"push_warehouse_id" NUMBER(19,0), 
"push_warehouse_bill" NVARCHAR2(50), 
"warehouse_id" NUMBER(19,0), 
"pw_start_dt" NVARCHAR2(50), 
"pw_end_dt" NVARCHAR2(50), 
"pack_id" NUMBER(19,0), 
"pack_desc" NVARCHAR2(50), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_PUSH_WAREHOUSE_DETAIL" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_service_evaluation" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"express_bill_id" NVARCHAR2(100), 
"express_bill" NUMBER(19,0), 
"pack_score" NUMBER(10,0), 
"delivery_time_score" NUMBER(10,0), 
"courier_score" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_SERVICE_EVALUATION" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_store_grid" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"warehouse_name" NVARCHAR2(50), 
"warehouse_addr" NVARCHAR2(100),
"warehouse_addr_gis" NVARCHAR2(50), 
"company_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_STORE_GRID" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_vehicle_monitor" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"delivery_record" NUMBER(19,0), 
"empId" NUMBER(19,0), 
"express_bill__id" NVARCHAR2(200), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_VEHICLE_MONITOR" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_warehouse_rack_map" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"warehouse_name" NVARCHAR2(50), 
"warehouse_addr" NVARCHAR2(100),
"warehouse_addr_gis" NVARCHAR2(50), 
"company_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WAREHOUSE_RACK_MAP" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_warehouse_receipt_detail" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"waybill_id" NUMBER(19,0), 
"pkg_id" NUMBER(19,0), 
"receipt_bill_id" NUMBER(19,0), 
"receipt_bill" NVARCHAR2(100), 
"operator_id" NUMBER(19,0), 
"state" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WAREHOUSE_RECEIPT_DETAI" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_waybill_line" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"waybill_number" NVARCHAR2(100), 
"route_id" NUMBER(19,0), 
"serial_number" NVARCHAR2(100), 
"transport_tool" NUMBER(19,0), 
"delivery_record_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WAYBILL_LINE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_waybill_state_record" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"waybill_id" NUMBER(19,0), 
"waybill_number" NVARCHAR2(100), 
"employee_id" NVARCHAR2(100), 
"consignee_id" NUMBER(19,0), 
"cur_warehouse_id" NUMBER(10,0), 
"next_warehouse_id" NUMBER(10,0), 
"deliverer_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WAYBILL_STATE_RECORD" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_work_time" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"name" NVARCHAR2(50), 
"start_dt" NVARCHAR2(100),
"stop_dt" NVARCHAR2(100),
"saturday_start_dt" NVARCHAR2(100),
"saturday_stop_dt" NVARCHAR2(100),
"sunday_start_dt" NVARCHAR2(100),
"sunday_stop_dt" NVARCHAR2(100),
"state" NUMBER(10,0), 
"company_id" NUMBER(10,0), 
"operator_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WORK_TIME" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_test" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"name" NVARCHAR2(50), 
 CONSTRAINT "PK_TBL_TEST" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_areas" (
"id" NUMBER(11,0) NOT NULL ENABLE, 
"name" NVARCHAR2(40), 
"pid" NUMBER(11,0), 
"sname" NVARCHAR2(40), 
"level" NVARCHAR2(11), 
"citycode" NVARCHAR2(20), 
"yzcode" NVARCHAR2(20), 
"mername" NVARCHAR2(100), 
"lng" NUMBER(11,4), 
"lat" NUMBER(11,4), 
"pinyin" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_AREAS" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_deliver_package" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"emp_id" NUMBER(19,0), 
"waybill_id" NUMBER(19,0), 
"waybill_number" NVARCHAR2(100), 
"express_bill_id" NUMBER(19,0), 
"express_bill_number" NVARCHAR2(100), 
"package_id" NUMBER(19,0), 
"collect_package_dt" DATE, 
"rece_type" NUMBER(19,0), 
"rece_dt" DATE, 
"state" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_DELIVER_PACKAGE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_customer" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"name" NVARCHAR2(50), 
"tel" NVARCHAR2(20), 
"mobile" NVARCHAR2(20), 
"email" NVARCHAR2(50), 
"type" NUMBER(10,0), 
"is_own_reg" NUMBER(10,0), 
"reg_dt" DATE, 
"reg_channel_id" NUMBER(10,0), 
"state" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"last_login_dt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_CUSTOMER" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_codes" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"name" NVARCHAR2(50), 
"type" NUMBER(19,0), 
"code" NVARCHAR2(50), 
"code_desc" NVARCHAR2(100), 
"code_type" NVARCHAR2(50), 
"state" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
 CONSTRAINT "PK_TBL_CODES" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_warehouse" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"name" NVARCHAR2(50), 
"addr" NVARCHAR2(19), 
"addr_gis" NVARCHAR2(50), 
"company_id" NUMBER(19,0), 
"employee_id" NUMBER(19,0), 
"type" NUMBER(10,0), 
"area" NVARCHAR2(50), 
"is_lease" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WAREHOUSE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_consumer_address_map" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"consumer_id" NUMBER(19,0), 
"address_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_CUSTOMER_SENDER_MAP" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_warehouse_receipt" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"bill" NVARCHAR2(100), 
"type" NUMBER(19,0), 
"warehouse_id" NUMBER(19,0), 
"operator_id" NUMBER(19,0), 
"state" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WAREHOUSE_RECEIPT" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_warehouse_send_vehicle" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"out_warehouse_id" NUMBER(19,0), 
"out_warehouse_waybill_id" NUMBER(19,0), 
"out_warehouse_waybill_number" NVARCHAR2(100), 
"vehicle_id" NUMBER(19,0), 
"driver1_id" NUMBER(19,0), 
"driver2_id" NUMBER(19,0), 
"start_vehicle_dt" DATE, 
"next_warehouse_id" NUMBER(19,0), 
"predict_arrivals_dt" DATE, 
"actual_arrivals_dt" DATE, 
"state" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WAREHOUSE_SEND_VEHICLE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_warehouse_vehicle_map" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"warehouse_id" NUMBER(19,0), 
"vehicle_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_COMPANY_VEHICLE_MAP" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_dot" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"dot_number" NVARCHAR2(50), 
"dot_name" NVARCHAR2(50), 
"dot_addr" NVARCHAR2(100), 
"dot_gis_addr" NVARCHAR2(100), 
"dot_tel" NVARCHAR2(20), 
"company_id" NUMBER(19,0), 
"manage_area_id" NUMBER(19,0), 
"manage_area_gis" NVARCHAR2(100), 
"state" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_DOT" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_transport_tool" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"brand" NVARCHAR2(100), 
"model" NVARCHAR2(100), 
"type" NUMBER(19,0), 
"given_load" NVARCHAR2(100), 
"load_cn_unit" NVARCHAR2(100), 
"load_en_unit" NVARCHAR2(100), 
"buy_dt" DATE, 
"license_plate" NVARCHAR2(100), 
"state" NVARCHAR2(100), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_TRANSPORT_TOOL" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_dot_transport_tool" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"dot_id" NUMBER(19,0), 
"transport_tool_id" NUMBER(19,0), 
"allocate_dt" DATE, 
"state" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_DOT_TRANSPORT_TOOL" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_address" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"name" NVARCHAR2(50), 
"tel" NVARCHAR2(20), 
"mobile" NVARCHAR2(20), 
"detail_addr" NVARCHAR2(100), 
"area_id" NUMBER(19,0), 
"gis_addr" NVARCHAR2(20), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_CUSTOMER_ADDRESS" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_route" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"start_station" NVARCHAR2(50), 
"start_station_area_id" NUMBER(19,0), 
"start_warehouse_id" NUMBER(19,0), 
"end_station" NVARCHAR2(50), 
"end_station_area_id" NUMBER(19,0), 
"end_warehouse_id" NUMBER(19,0), 
"mileage_m" NUMBER(10,0), 
"time_consumer_minute" NUMBER(10,0), 
"state" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_ROUTE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_push_warehouse" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"pw_waybill_id" NUMBER(19,0), 
"pw_waybill_number" NVARCHAR2(50), 
"pw_dot_id" NUMBER(19,0), 
"warehouse_id" NUMBER(19,0), 
"emp_id" NUMBER(19,0), 
"pw_start_dt" DATE, 
"pw_end_dt" DATE, 
"pw_position" NVARCHAR2(50), 
"pw_reg_emp_id" NUMBER(19,0), 
"ow_reg_emp_scan_gun_id" NUMBER(19,0), 
"pw_confirm_emp_id" NUMBER(19,0), 
"ow_confirm_emp_scan_gun_id" NUMBER(19,0), 
"pw_box_emp_id" NUMBER(19,0), 
"pw_box_scan_gun_id" NUMBER(19,0), 
"pw_after_seal_img" NVARCHAR2(100), 
"pw_receipt_number" NVARCHAR2(100), 
"pw_receipt_dt" DATE, 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_PUSH_WAREHOUSE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_out_warehouse" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"pw_waybill_id" NUMBER(19,0), 
"pw_waybill_number" NVARCHAR2(100), 
"ow_dot_id" NUMBER(19,0), 
"warehouse_id" NUMBER(19,0), 
"ow_vehicle_id" NUMBER(19,0), 
"ow_driver_emp_id" NUMBER(19,0), 
"ow_follow1_emp_id" NUMBER(19,0), 
"ow_follow2_emp_id" NUMBER(19,0), 
"ow_start_dt" DATE, 
"ow_end_dt" DATE, 
"ow_position" NVARCHAR2(50), 
"ow_reg_emp_id" NUMBER(19,0), 
"ow_reg_scan_gun_id" NUMBER(19,0), 
"ow_confirm_emp_id" NUMBER(19,0), 
"ow_confirm_scan_gun_id" NUMBER(19,0), 
"ow_pre_seal_img" NVARCHAR2(100), 
"ow_receipt_number" NVARCHAR2(100), 
"ow_receipt_dt" DATE, 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_OUT_WAREHOUSE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_warehouse_emp" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"job_num" NVARCHAR2(50), 
"name" NVARCHAR2(50), 
"birathday" DATE, 
"tel" NVARCHAR2(20), 
"type" NUMBER(10,0), 
"warehouse_id" NUMBER(19,0), 
"state" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WAREHOUSE_EMP" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_express_package" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"scan_gun_id" NVARCHAR2(19), 
"name" NVARCHAR2(50), 
"cid" NUMBER(10,2), 
"weight" NUMBER(10,2), 
"amount" NUMBER(10,2), 
"coupon_id" NUMBER(19,0), 
"coupon_amount" NUMBER(10,2), 
"actual_amount" NUMBER(10,2), 
"insured_price" NUMBER(10,2), 
"is_fragile" NVARCHAR2(20), 
"send_address_id" NUMBER(19,0), 
"recv_address_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_EXPRESS_PACKAGE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_express_bill" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"express_number" NVARCHAR2(50), 
"cid" NUMBER(19,0), 
"eid" NUMBER(19,0), 
"order_channel_id" NUMBER(19,0), 
"order_dt" DATE, 
"order_terminal_type" NUMBER(10,0), 
"order_terminal_os_type" NUMBER(10,0), 
"reserve_dt" DATE, 
"is_collect_package_timeout" NUMBER(10,0), 
"timeout_dt" DATE, 
"type" NUMBER(10,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_EXPRESS_BILL" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_consumer_sender_info" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"ciid" NUMBER(19,0), 
"pkg_id" NUMBER(19,0), 
"express_bill_id" NUMBER(19,0), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_CUSTOMER_SENDER_INFO" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_collect_package" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"cid" NUMBER(19,0), 
"eid" NUMBER(19,0), 
"pkg_id" NUMBER(19,0), 
"express_bill_id" NUMBER(19,0), 
"express_bill_number" NVARCHAR2(100), 
"state" NUMBER(10,0), 
"collect_package_dt" DATE, 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_COLLECT_PACKAGE" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS"; 

CREATE TABLE "tbl_waybill" (
"id" NUMBER(19,0) NOT NULL ENABLE, 
"express_bill_number" NVARCHAR2(100), 
"waybill_number" NVARCHAR2(100), 
"cid" NUMBER(19,0), 
"eid" NUMBER(19,0), 
"order_channel_id" NUMBER(19,0), 
"order_dt" DATE, 
"order_terminal_type" NUMBER(10,0), 
"order_terminal_os_type" NUMBER(10,0), 
"reserve_dt" DATE, 
"is_collect_package_timeout" NUMBER(10,0), 
"pkg_id" NUMBER(19,0), 
"pkg_number" NVARCHAR2(100), 
"timeout_dt" NVARCHAR2(100), 
"transform_type" NUMBER(10,0), 
"delivery_customer_name" NVARCHAR2(100), 
"delivery_addr" NVARCHAR2(100), 
"delivery_mobile" NVARCHAR2(100), 
"delivery_tel" NVARCHAR2(100), 
"receive_customer_name" NVARCHAR2(100), 
"receive_addr" NVARCHAR2(100), 
"receive_mobile" NVARCHAR2(100), 
"receive_tel" NVARCHAR2(100), 
"cdt" DATE, 
"udt" DATE, 
"remark" NVARCHAR2(100), 
 CONSTRAINT "PK_TBL_WAYBILL" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS";

-- 運輸記錄表
CREATE TABLE "tbl_transport_record" (  
   "id" NUMBER(19,0) NOT NULL ENABLE, 
   "pw_id" NUMBER(19,0), 
   "pw_waybill_id" NUMBER(19,0), 
   "pw_waybill_number" NVARCHAR2(100), 
   "ow_id" NUMBER(19,0), 
   "ow_waybill_id" NUMBER(19,0), 
   "ow_waybill_number" NVARCHAR2(100), 
   "sw_id" NUMBER(19,0), 
   "ew_id" NUMBER(19,0), 
   "transport_tool_id" NUMBER(19,0), 
   "pw_driver1_id" NUMBER(19,0), 
   "pw_driver2_id" NUMBER(19,0), 
   "pw_driver3_id" NUMBER(19,0), 
   "ow_driver1_id" NUMBER(19,0), 
   "ow_driver2_id" NUMBER(19,0), 
   "ow_driver3_id" NUMBER(19,0), 
   "route_id" NUMBER(19,0), 
   "distance" NUMBER(10,0), 
   "duration" NUMBER(10,0), 
   "state" NUMBER(10,0), 
   "start_vehicle_dt" DATE, 
   "predict_arrivals_dt" DATE, 
   "actual_arrivals_dt" DATE, 
   "cdt" DATE, 
   "udt" DATE, 
   "remark" NVARCHAR2(100), 
    CONSTRAINT "PK_TBL_TRANSPORT_RECORD" PRIMARY KEY ("id")
) TABLESPACE "TBS_LOGISTICS";

2、配置管理器MGR行程

  • 進入源端OGG命令列

./ggsci

創建mgr行程：edit param mgr

PORT 7809

DYNAMICPORTLIST 7810-7909

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

3、???????配置extract行程

配置Extract行程：edit param extkafka

新增內容：

extract extkafka

GETUPDATEBEFORES

NOCOMPRESSDELETES

NOCOMPRESSUPDATES

dynamicresolution

SETENV (ORACLE_SID = "orcl")

SETENV (NLS_LANG = "american_america.AL32UTF8")

userid ogg,password ogg

exttrail /u01/app/ogg/src/dirdat/to

table ogg.*;

引數名稱

引數說明

extract extkafka

定義extract行程名稱

dynamicresolution

啟用動態決議

SETENV (ORACLE_SID = "orcl")

設定Oracle資料庫

SETENV (NLS_LANG = "american_america.AL32UTF8")

設定字符集

userid ogg,password ogg

ogg連接Oracle資料庫的帳號密碼

exttrail /u01/app/ogg/src/dirdat/to

定義trail檔案的保存位置以及檔案名，檔案字母最多2個，否則會報錯

table ogg.*;

復制表的表名，支持*通配，必須以;結尾

4、配置pump行程

配置Pump行程：edit param pukafka

新增內容：

extract pukafka

passthru

dynamicresolution

userid ogg,password ogg

rmthost localhost mgrport 7809

rmttrail /u01/app/ogg/tgr/dirdat/to

table ogg.*;

extract行程名稱；passthru即禁止OGG與Oracle互動，我們這里使用pump邏輯傳輸，故禁止即可；dynamicresolution動態決議；userid ogg,password ogg即OGG連接Oracle資料庫的帳號密碼rmthost和mgrhost即目標端(kafka)OGG的mgr服務的地址以及監聽埠；rmttrail即目標端trail檔案存盤位置以及名稱，

引數名稱

引數說明

extract pukafka

定義pump行程名稱

passthru

因使用了pump邏輯傳輸，所以禁止OGG與Oracle互動

dynamicresolution

配置動態決議

userid ogg,password ogg

OGG連接Oracle資料庫的帳號密碼

rmthost localhost mgrport 7809

目標端OGG的mgr服務的地址以及監聽埠

rmttrail /u01/app/ogg/tgr/dirdat/to

目標端OGG的trail檔案存盤位置以及名稱

table ogg.*;

要采集的表,必須使用;結尾

5、???????配置define檔案

注意：該檔案用來在異構資料源之間傳輸時，需明確知道表之間的映射關系，比如：

Oracle與MySQL，Hadoop集群（HDFS，Hive，kafka等）等之間資料傳輸可以定義為異構資料型別的傳輸，故需要定義表之間的關系映射，在OGG命令列執行：

配置define檔案：edit param test_ogg

defsfile /u01/app/ogg/src/dirdef/test_ogg.test_ogg

userid ogg,password ogg

table ogg.*;

生成表schema檔案：（在OGG_SRC_HOME目錄下執行(oracle用戶)）

./defgen paramfile dirprm/test_ogg.prm

將生成的/u01/app/ogg/src/dirdef/test_ogg.test_ogg發送的目標端ogg目錄下的dirdef里：

scp -r /u01/app/ogg/src/dirdef/test_ogg.test_ogg /u01/app/ogg/tgr/dirdef/

因為目標端目錄還沒有創建，因此發送檔案可能會失敗，所以執行完目標端配置后發送即可

三、配置OGG目標端

1、???????拷貝源端的define檔案到目標端

將生成的/u01/app/ogg/src/dirdef/test_ogg.test_ogg發送的目標端ogg目錄下的dirdef里：

scp -r $OGG_SRC_HOME/dirdef/test_ogg.test_ogg $OGG_TGR_HOME/dirdef/

2、???????配置管理器MRG行程

操作步驟

說明

1

使用oracle用戶進入OGG_SRC_HOME目錄下

切換到oracle用戶下：su – oracle

列印目標端OGG_TGR_HOME：echo $OGG_TGR_HOME

進入OGG_TGR_HOME：cd $OGG_TGR_HOME

啟動ggsci：./ggsci

2

配置目標端MRG行程

配置MGR行程：edit param mgr

新增內容：

PORT 7810

DYNAMICPORTLIST 7810-7909

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

3、配置Replicate行程

操作步驟

說明

1

配置目標端Replicate行程

配置replicate行程：edit param rekafka

REPLICAT rekafka

sourcedefs /u01/app/ogg/tgr/dirdef/test_ogg.test_ogg

TARGETDB LIBFILE libggjava.so SET property=dirprm/kafka.props

REPORTCOUNT EVERY 1 MINUTES, RATE

GROUPTRANSOPS 10000

MAP ogg.*, TARGET ogg.*;

4、???????配置kafka.props

  • 配置kafka.props

cd $OGG_TGR_HOME

vim dirprm/kafka.props

新增內容：

gg.handlerlist=kafkahandler

gg.handler.kafkahandler.type=kafka

gg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties

gg.handler.kafkahandler.topicMappingTemplate=logistics

gg.handler.kafkahandler.format=json

gg.handler.kafkahandler.mode=op

gg.classpath=dirprm/:/export/services/kafka/libs/*:/u01/app/ogg/tgr/:/u01/app/ogg/tgr/lib/*

  • 配置custom_kafka_producer.properties

cd $OGG_TGR_HOME

vim dirprm/custom_kafka_producer.properties

新增內容：

bootstrap.servers=node2:9092

acks=1

compression.type=gzip

reconnect.backoff.ms=1000

value.serializer=org.apache.kafka.common.serialization.ByteArraySerializer

key.serializer=org.apache.kafka.common.serialization.ByteArraySerializer

batch.size=102400

linger.ms=10000

???????5、最后確認所有的行程

在目標端，主要做了4個操作，共包括2個行程，分別是MANAGER和REPLICAT，

四、???????OGG測驗

1、???????啟動kafka消費者

操作步驟

說明

1

啟動node2服務器（大資料服務器）

2

啟動kafka消費者

kafka-console-consumer --bootstrap-server node2:9092 --topic logistics

3

啟動oracle客戶端

插入一條資料

INSERT INTO OGG."tbl_company"("id", "company_name", "city_id", "company_number", "company_addr", "company_addr_gis", "company_tel", "is_sub_company", "state", "cdt", "udt", "remark")

VALUES(11, '廣州速遞郵箱公司', 440100, NULL, '廣州', '117.28177895734918_31.842711680531399', NULL, 1, 1, TO_DATE('2020-06-13 15:24:51','yyyy-mm-dd hh24:mi:ss'), TO_DATE('2020-06-13 15:24:51','yyyy-mm-dd hh24:mi:ss'), NULL);

4

查看kafka消費者是否可以列印出來日志

修改一條資料

UPDATE OGG."tbl_company" SET "company_name"='廣州速遞有限公司-1' WHERE "id"=11;

2、???????archivelog日志路徑

  • 📢博客主頁：https://lansonli.blog.csdn.net
  • 📢歡迎點贊 👍 收藏 ?留言 📝 如有錯誤敬請指正！
  • 📢本文由 Lansonli 原創，首發于 CSDN博客🙉
  • 📢大資料系列文章會每天更新，停下休息的時候不要忘了別人還在奔跑，希望大家抓緊時間學習，全力奔赴更美好的生活?

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/423335.html

標籤：其他

上一篇：通過Spring Data Elasticsearch操作ES

下一篇：Flink學習之流處理架構

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • 網閘典型架構簡述

    網閘架構一般分為兩種：三主機的三系統架構網閘和雙主機的2+1架構網閘。 三主機架構分別為內端機、外端機和仲裁機。三機無論從軟體和硬體上均各自獨立。首先從硬體上來看，三機都用各自獨立的主板、記憶體及存盤設備。從軟體上來看，三機有各自獨立的作業系統。這樣能達到完全的三機獨立。對于“2+1”系統，“2”分為 ......

    uj5u.com 2020-09-10 02:00:44 more
  • 如何從xshell上傳檔案到centos linux虛擬機里

    如何從xshell上傳檔案到centos linux虛擬機里及：虛擬機CentOs下執行 yum -y install lrzsz命令，出現錯誤：鏡像無法找到軟體包 前言 一、安裝lrzsz步驟 二、上傳檔案 三、遇到的問題及解決方案 總結 前言 提示：其實很簡單，往虛擬機上安裝一個上傳檔案的工具 ......

    uj5u.com 2020-09-10 02:00:47 more
  • 一、SQLMAP入門

    一、SQLMAP入門 1、判斷是否存在注入 sqlmap.py -u 網址/id=1 id=1不可缺少。當注入點后面的引數大于兩個時。需要加雙引號， sqlmap.py -u "網址/id=1&uid=1" 2、判斷文本中的請求是否存在注入 從文本中加載http請求，SQLMAP可以從一個文本檔案中 ......

    uj5u.com 2020-09-10 02:00:50 more
  • Metasploit 簡單使用教程

    metasploit 簡單使用教程 浩先生， 2020-08-28 16:18:25 分類專欄： kail 網路安全 linux 文章標簽： linux資訊安全 編輯 著作權 metasploit 使用教程 前言 一、Metasploit是什么？ 二、準備作業 三、具體步驟 前言 Msfconsole ......

    uj5u.com 2020-09-10 02:00:53 more
  • 游戲逆向之驅動層與用戶層通訊

    驅動層代碼： #pragma once #include <ntifs.h> #define add_code CTL_CODE(FILE_DEVICE_UNKNOWN,0x800,METHOD_BUFFERED,FILE_ANY_ACCESS) /* 更多游戲逆向視頻www.yxfzedu.com ......

    uj5u.com 2020-09-10 02:00:56 more
  • 北斗電力時鐘（北斗授時服務器）讓網路資料更精準

    北斗電力時鐘（北斗授時服務器）讓網路資料更精準 北斗電力時鐘（北斗授時服務器）讓網路資料更精準 京準電子科技官微——ahjzsz 近幾年，資訊技術的得了快速發展，互聯網在逐漸普及，其在人們生活和生產中都得到了廣泛應用，并且取得了不錯的應用效果。計算機網路資訊在電力系統中的應用，一方面使電力系統的運行 ......

    uj5u.com 2020-09-10 02:01:03 more
  • 【CTF】CTFHub 技能樹 彩蛋 writeup

    ?碎碎念 CTFHub：https://www.ctfhub.com/ 筆者入門CTF時時剛開始刷的是bugku的舊平臺，后來才有了CTFHub。 感覺不論是網頁UI設計，還是題目質量，賽事跟蹤，工具軟體都做得很不錯。 而且因為獨到的金幣制度的確讓人有一種想去刷題賺金幣的感覺。 個人還是非常喜歡這個 ......

    uj5u.com 2020-09-10 02:04:05 more
  • 02windows基礎操作

    我學到了一下幾點 Windows系統目錄結構與滲透的作用 常見Windows的服務詳解 Windows埠詳解 常用的Windows注冊表詳解 hacker DOS命令詳解（net user / type /md /rd/ dir /cd /net use copy、批處理 等） 利用dos命令制作 ......

    uj5u.com 2020-09-10 02:04:18 more
  • 03.Linux基礎操作

    我學到了以下幾點 01Linux系統介紹02系統安裝，密碼啊破解03Linux常用命令04LAMP 01LINUX windows： win03 8 12 16 19 配置不繁瑣 Linux：redhat,centos(紅帽社區版)，Ubuntu server,suse unix:金融機構，證券，銀 ......

    uj5u.com 2020-09-10 02:04:30 more
  • 05HTML

    01HTML介紹 02頭部標簽講解03基礎標簽講解04表單標簽講解 HTML前段語言 js1.了解代碼2.根據代碼 懂得挖掘漏洞 （POST注入/XSS漏洞上傳）3.黑帽seo 白帽seo 客戶網站被黑帽植入劫持代碼如何處理4.熟悉html表單 <html><head><title>TDK標題，描述 ......

    uj5u.com 2020-09-10 02:04:36 more
最新发布
  • 2023年最新微信小程式抓包教程

    01 開門見山 隔一個月發一篇文章，不過分。 首先回顧一下《微信系結手機號資料庫被脫庫事件》，我也是第一時間得知了這個訊息，然后跟蹤了整件事情的經過。下面是這起事件的相關截圖以及近日流出的一萬條資料樣本： 個人認為這件事也沒什么，還不如關注一下之前45億快遞資料查詢渠道疑似在近日復活的訊息。 訊息是 ......

    uj5u.com 2023-04-20 08:48:24 more
  • web3 產品介紹：metamask 錢包 使用最多的瀏覽器插件錢包

    Metamask錢包是一種基于區塊鏈技術的數字貨幣錢包，它允許用戶在安全、便捷的環境下管理自己的加密資產。Metamask錢包是以太坊生態系統中最流行的錢包之一，它具有易于使用、安全性高和功能強大等優點。 本文將詳細介紹Metamask錢包的功能和使用方法。 一、 Metamask錢包的功能 數字資 ......

    uj5u.com 2023-04-20 08:47:46 more
  • vulnhub_Earth

    前言 靶機地址->>>vulnhub_Earth 攻擊機ip：192.168.20.121 靶機ip：192.168.20.122 參考文章 https://www.cnblogs.com/Jing-X/archive/2022/04/03/16097695.html https://www.cnb ......

    uj5u.com 2023-04-20 07:46:20 more
  • 從4k到42k，軟體測驗工程師的漲薪史，給我看哭了

    清明節一過，盲猜大家已經無心上班，在數著日子準備過五一，但一想到銀行卡里的余額……瞬間心情就不美麗了。最近，2023年高校畢業生就業調查顯示，本科畢業月平均起薪為5825元。調查一出，便有很多同學表示自己又被平均了。看著這一資料，不免讓人想到前不久中國青年報的一項調查：近六成大學生認為畢業10年內會 ......

    uj5u.com 2023-04-20 07:44:00 more
  • 最新版本 Stable Diffusion 開源 AI 繪畫工具之中文自動提詞篇

    🎈 標簽生成器 由于輸入正向提示詞 prompt 和反向提示詞 negative prompt 都是使用英文，所以對學習母語的我們非常不友好 使用網址：https://tinygeeker.github.io/p/ai-prompt-generator 這個網址是為了讓大家在使用 AI 繪畫的時候 ......

    uj5u.com 2023-04-20 07:43:36 more
  • 漫談前端自動化測驗演進之路及測驗工具分析

    隨著前端技術的不斷發展和應用程式的日益復雜，前端自動化測驗也在不斷演進。隨著 Web 應用程式變得越來越復雜，自動化測驗的需求也越來越高。如今，自動化測驗已經成為 Web 應用程式開發程序中不可或缺的一部分，它們可以幫助開發人員更快地發現和修復錯誤，提高應用程式的性能和可靠性。 ......

    uj5u.com 2023-04-20 07:43:16 more
  • CANN開發實踐：4個DVPP記憶體問題的典型案例解讀

    摘要：由于DVPP媒體資料處理功能對存放輸入、輸出資料的記憶體有更高的要求（例如，記憶體首地址128位元組對齊），因此需呼叫專用的記憶體申請介面，那么本期就分享幾個關于DVPP記憶體問題的典型案例，并給出原因分析及解決方法。 本文分享自華為云社區《FAQ_DVPP記憶體問題案例》，作者：昇騰CANN。 DVPP ......

    uj5u.com 2023-04-20 07:43:03 more
  • msf學習

    msf學習 以kali自帶的msf為例 一、msf核心模塊與功能 msf模塊都放在/usr/share/metasploit-framework/modules目錄下 1、auxiliary 輔助模塊，輔助滲透（埠掃描、登錄密碼爆破、漏洞驗證等） 2、encoders 編碼器模塊，主要包含各種編碼 ......

    uj5u.com 2023-04-20 07:42:59 more
  • Halcon軟體安裝與界面簡介

    1. 下載Halcon17版本到到本地 2. 雙擊安裝包后 3. 步驟如下 1.2 Halcon軟體安裝 界面分為四大塊 1. Halcon的五個助手 1) 影像采集助手：與相機連接，設定相機引數，采集影像 2) 標定助手：九點標定或是其它的標定，生成標定檔案及內參外參，可以將像素單位轉換為長度單位 ......

    uj5u.com 2023-04-20 07:42:17 more
  • 在MacOS下使用Unity3D開發游戲

    第一次發博客，先發一下我的游戲開發環境吧。 去年2月份買了一臺MacBookPro2021 M1pro(以下簡稱mbp)，這一年來一直在用mbp開發游戲。我大致分享一下我的開發工具以及使用體驗。 1、Unity 官網鏈接： https://unity.cn/releases 我一般使用的Apple ......

    uj5u.com 2023-04-20 07:40:19 more

Copyright © 2010-2020 有解無憂