neo4j两种同步数据方式

文章目录

• apoc方式同步jdbc
• • 1. call apoc
  • 2. 步骤
• neo4j-import方式全量同步
• • 1. 导出csv文件
  • 2. 执行import命令
  • 3. 切换数据库,修改配置文件conf/neo4j.conf
  • 4. 重启neo4j

apoc方式同步jdbc

1. call apoc

CALL apoc.load.jdbc('jdbc:mysql://172.16.4.83:3306/ibmp?user=ibmp_test&password=XZdfjXIEsrumGrfFTmfltbtAuQCECUdl&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC', 'select * from basic_community') YIELD row

2. 步骤

• 创建索引

CREATE INDEX ON :BasicCommunity(community_id);
CREATE INDEX ON :BasicDevices(device_id);
CREATE INDEX ON :BasicDevices(community_id);

• apoc查询jdbc数据，最近一小时

select * from basic_person where upd_time >= from_unixtime(${updateTime}-1800) or crt_time >= from_unixtime(${updateTime}-1800);

• 创建/更新节点、关系

//apoc.load.jdbc在一个会话里不建议多次调用
//小区
with "jdbc:mysql://172.16.4.83:3306/ibmp?user=ibmp_test&password=111111&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC" as url
//CALL apoc.load.jdbc(url, 'select * from basic_community where community_id="420100000009" or community_id="420100000012"') YIELD row 
CALL apoc.load.jdbc(url, 'select * from basic_community') YIELD row 
MERGE (c:BasicCommunity{id:row.id})
on create set c.community_id=row.community_id,c.community_name=row.community_name,c.crt_time=datetime(),c.del_flag=row.del_flag
on match set c.community_id=row.community_id,c.community_name=row.community_name,c.upd_time=datetime(),c.del_flag=row.del_flag
//with c
//match (m:BasicDevices) where m.del_flag='0' and m.community_id is not null and m.community_id=c.community_id
//match (j:BasicCommunity) where j.del_flag='0' and j.community_id=m.community_id
//merge (m)-[b:BELONG_TO]->(j)
//merge (j)-[o:HAS]->(m)
//with '' as x
//MATCH (d:BasicCommunity{del_flag:'1'}) 
//DETACH DELETE d
//设备
with "jdbc:mysql://172.16.4.83:3306/ibmp?user=ibmp_test&password=111111&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC" as url
//CALL apoc.load.jdbc(url, 'select * from basic_devices where device_id="004A770124002EC5" or device_id="WHJK000201RL0001"') YIELD row
CALL apoc.load.jdbc(url, 'select * from basic_devices') YIELD row
MERGE (c:BasicDevices{id:row.id})
on create set c.device_id=row.device_id,c.community_id=row.community_id,c.device_name=row.device_name,c.crt_time=datetime(),c.del_flag=row.del_flag
on match set c.device_id=row.device_id,c.community_id=row.community_id,c.device_name=row.device_name,c.upd_time=datetime(),c.del_flag=row.del_flag
//with c
//match (m:BasicDevices) where m.del_flag='0' and m.community_id is not null and m.id=c.id
//match (j:BasicCommunity) where j.del_flag='0' and j.community_id=m.community_id
//merge (m)-[b:BELONG_TO]->(j)
//merge (j)-[o:HAS]->(m)
//with '' as x
//MATCH (d:BasicDevices{del_flag:'1'}) 
//DETACH DELETE d
//人员
with "jdbc:mysql://172.16.4.83:3306/ibmp?user=ibmp_test&password=111111&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC" as url
CALL apoc.load.jdbc(url, 'select * from basic_person') YIELD row
MERGE (c:BasicPerson{id:row.id})
on create set c.person_id=row.person_id,c.name=row.name,c.crt_time=datetime(),c.del_flag=row.del_flag
on match set c.person_id=row.person_id,c.name=row.name,c.upd_time=datetime(),c.del_flag=row.del_flag
with '' as x
MATCH (d:BasicPerson{del_flag:'1'}) 
DETACH DELETE d

• 删除节点、关系

//小区
with '' as x
MATCH (d:BasicCommunity{del_flag:'1'}) 
DETACH DELETE d
//设备
with '' as x
MATCH (d:BasicDevices{del_flag:'1'}) 
DETACH DELETE d

• 建立关系

match (n:BasicCommunity)
match (m:BasicDevices) where m.del_flag='0' and m.community_id is not null and n.community_id=m.community_id
merge (m)-[b:BELONG_TO]->(n)
merge (n)-[o:HAS]->(m)

neo4j-import方式全量同步

  全量同步之前需要保证同步的数据库是新建的或者清空数据的

rm -rf /opt/neo4j-community-3.5.26/data/databases/testcz.db/ 

1. 导出csv文件

• 导出节点

-- basic_car
select id as `id:ID`, license, person_id, 'TestBasicCar4' as `:LABEL` from basic_car where del_flag='0';
-- basic_person
select id as `id:ID`, `name`,person_id,'TestBasicPerson4' as `:LABEL` from basic_person where del_flag='0' and person_id in ('202cb962ac59075b964b07152d234b70', 'f703da8059a19b0bde6c4eb857de5f50', 'ffc2a2b9066cb5119d41b367d3027060', '35314df2291a7ba05851ec60beef5a35');

• 导出关系

select a.id as `:START_ID`, b.id as `:END_ID`, '属于' as `:TYPE` from basic_car a INNER JOIN basic_person b on a.person_id=b.person_id where a.del_flag='0' and b.del_flag='0';

2. 执行import命令

bin/neo4j-admin import --mode csv --database=testcz.db --ignore-extra-columns=true  --ignore-missing-nodes=true --ignore-duplicate-nodes=true --nodes:TestBasicPerson4 '/opt/neo4j-community-3.5.26/import/TestPerson4.csv' --nodes:TestBasicCar4 '/opt/neo4j-community-3.5.26/import/TestBasicCar4.csv' --relationships:属于 '/opt/neo4j-community-3.5.26/import/TestPersonCarRel4.csv'

3. 切换数据库,修改配置文件conf/neo4j.conf

dbms.active_database=testcz.db

4. 重启neo4j

bin/neo4j restart