예제 #1
0
 def insert_nodes_orders(self):
     graphDB = InitilizeDB.init()
     try:
         graphDB.run(
             'CREATE CONSTRAINT ON (o:ORDER) ASSERT o.id IS UNIQUE;')
     except:
         pass
     graphDB.run(
         'USING PERIODIC COMMIT '
         'LOAD CSV WITH HEADERS '
         'FROM "file:///orders_neo4j.csv" AS line FIELDTERMINATOR "|" '
         # 'WITH DISTINCT line, SPLIT(line.O_ORDERDATE, " / ") AS date '
         'CREATE (order:ORDER { id: TOINTEGER(line.O_ORDERKEY) }) '
         'SET order.O_CUSTKEY = TOINTEGER(line.O_CUSTKEY),'
         '    order.O_ORDERSTATUS = line.O_ORDERSTATUS,	'
         '    order.O_ORDERKEY = line.O_ORDERKEY,	'
         '    order.O_TOTALPRICE = TOFLOAT(line.O_TOTALPRICE),'
         '    order.O_ORDERDATE = line.O_ORDERDATE,'
         '    order.O_ORDERPRIORITY = line.O_ORDERPRIORITY,'
         '    order.O_CLERK = line.O_CLERK,'
         '    order.O_SHIPPRIORITY = line.O_SHIPPRIORITY,'
         '    order.O_COMMENT = line.O_COMMENT,'
         '    order.O_YEAR = TOINTEGER(line.O_YEAR),'
         '    order.O_MONTH = TOINTEGER(line.O_MONTH),'
         '    order.O_DAY = TOINTEGER(line.O_DAY);')
예제 #2
0
    def insert_relation_part_partsupp(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS FROM "file:///rel_part_partsupp.csv" AS row FIELDTERMINATOR  "|" '
            'MATCH (partsupp:PARTSUPP {PS_PARTKEY: toInteger(row.P_PARTKEY)}) '
            'MATCH (part:PART {id: toInteger(row.P_PARTKEY)}) '
            'MERGE (partsupp)-[:COMPOSED_BY_2]->(part); ')
예제 #3
0
    def insert_relation_nation_supplier(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS FROM "file:///rel_nation_supplier.csv" AS row FIELDTERMINATOR "|" '
            'MATCH (supplier:SUPPLIER {id: toInteger(row.S_SUPPKEY)}) '
            'MATCH (nation:NATION {id: toInteger(row.S_NATIONKEY)}) '
            'MERGE (supplier)-[:BELONGS_TO_1]->(nation); ')
예제 #4
0
    def insert_relation_nation_region(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS FROM "file:///rel_nation_region.csv" AS row FIELDTERMINATOR "|" '
            'MATCH (nation:NATION {id: toInteger(row.N_NATIONKEY)}) '
            'MATCH (region:REGION {id: toInteger(row.N_REGIONKEY)}) '
            'MERGE (nation)-[:FROM_10]->(region); ')
예제 #5
0
    def insert_relation_customer_nation(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS FROM "file:///rel_customer_nation.csv" AS row FIELDTERMINATOR "|" '
            'MATCH (customer:CUSTOMER {id: toInteger(row.C_CUSTKEY)}) '
            'MATCH (nation:NATION {id: toInteger(row.C_NATIONKEY)}) '
            'MERGE (customer)-[:FROM_4]->(nation); ')
예제 #6
0
    def insert_relation_orders_customer(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS FROM "file:///rel_orders_customer.csv" AS row FIELDTERMINATOR "|" '
            'MATCH (orders:ORDER {id: toInteger(row.O_ORDERKEY)}) '
            'MATCH (customer:CUSTOMER {id: toInteger(row.O_CUSTKEY)}) '
            'MERGE (orders)-[:BY_5]->(customer); ')
예제 #7
0
    def insert_relation_supplier_partsupp(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS FROM "file:///rel_supplier_partsupp.csv" AS row FIELDTERMINATOR "|" '
            'MATCH (partsupp:PARTSUPP {PS_PARTKEY:toInteger(row.PS_PARTKEY) , PS_SUPPKEY: '
            'toInteger(row.PS_SUPPKEY)}) '
            'MATCH (supplier:SUPPLIER {id: toInteger(row.PS_SUPPKEY)}) '
            'MERGE (partsupp)-[:SUPPLIED_BY_3]->(supplier); ')
예제 #8
0
    def insert_relation_lineitem_supplier(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS FROM "file:///rel_lineitem_supplier.csv" AS row FIELDTERMINATOR "|" '
            'MATCH (lineitem:LINEITEM {L_ORDERKEY: toInteger(row.L_ORDERKEY), L_PARTKEY:'
            'toInteger(row.L_PARTKEY), L_SUPPKEY: toInteger(row.L_SUPPKEY),L_LINENUMBER:'
            'toInteger(row.L_LINENUMBER)  }) '
            'MATCH (supplier:SUPPLIER {id: toInteger(row.L_SUPPKEY)}) '
            'MERGE (lineitem)-[:SUPPLIED_BY_9]->(supplier);')
예제 #9
0
    def insert_relation_lineitem_orders(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS FROM "file:///rel_lineitem_orders.csv" AS row FIELDTERMINATOR "|" '
            'MATCH (lineitem:LINEITEM {L_ORDERKEY: toInteger(row.L_ORDERKEY), L_PARTKEY: '
            'toInteger(row.L_PARTKEY), L_SUPPKEY: toInteger(row.L_SUPPKEY),L_LINENUMBER: '
            'toInteger(row.L_LINENUMBER) }) '
            'MATCH (orders:ORDER {id: toInteger(row.L_ORDERKEY), O_CUSTKEY: toInteger(row.O_CUSTKEY)}) '
            'MERGE (lineitem)-[:BELONGS_TO_7]->(orders); ')
예제 #10
0
    def insert_relation_lineitem_part(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS FROM "file:///rel_lineitem_part.csv" AS row FIELDTERMINATOR "|" '
            'MATCH (lineitem:LINEITEM {L_ORDERKEY: toInteger(row.L_ORDERKEY), L_PARTKEY: '
            'toInteger(row.L_PARTKEY), L_SUPPKEY: toInteger(row.L_SUPPKEY),L_LINENUMBER: '
            'toInteger(row.L_LINENUMBER) }) '
            'MATCH (part:PART {id: toInteger(row.L_PARTKEY), P_NAME: row.P_NAME }) '
            'MERGE (lineitem)-[:COMPOSED_BY_8]->(part); ')
예제 #11
0
 def insert_nodes_region(self):
     graphDB = InitilizeDB.init()
     try:
         graphDB.run(
             'CREATE CONSTRAINT ON (r:REGION) ASSERT r.id IS UNIQUE;')
     except:
         pass
     graphDB.run(
         'USING PERIODIC COMMIT '
         'LOAD CSV WITH HEADERS '
         'FROM "file:///region.csv" AS line FIELDTERMINATOR "|"'
         'CREATE (region:REGION { id: TOINTEGER(line.R_REGIONKEY) }) '
         'SET region.R_NAME = line.R_NAME,	'
         '    region.R_COMMENT = line.R_COMMENT; ')
예제 #12
0
    def insert_nodes_partsupp(self):
        graphDB = InitilizeDB.init()

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS '
            'FROM "file:///partsupp.csv" AS line FIELDTERMINATOR "|" '
            'CREATE (partsupp:PARTSUPP { PS_PARTKEY: TOINTEGER(line.PS_PARTKEY) }) '
            'SET partsupp.PS_SUPPKEY = TOINTEGER(line.PS_SUPPKEY), '
            '    partsupp.PS_AVAILQTY = TOINTEGER(line.PS_AVAILQTY), '
            '    partsupp.PS_SUPPLYCOST = TOFLOAT(line.PS_SUPPLYCOST), '
            '    partsupp.PS_COMMENT = line.PS_COMMENT; ')
        graphDB.run('CREATE INDEX ON :PARTSUPP(PS_PARTKEY); ')
        graphDB.run('CREATE INDEX ON :PARTSUPP(PS_SUPPKEY); ')
예제 #13
0
    def insert_nodes_nation(self):

        graphDB = InitilizeDB.init()
        # graphDB.run(
        #     'CREATE CONSTRAINT ON (n:NATION) ASSERT n.id IS UNIQUE; '
        # )
        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS '
            'FROM "file:///nation.csv" AS line FIELDTERMINATOR "|" '
            'CREATE (nation:NATION { id: TOINTEGER(line.N_NATIONKEY) }) '
            'SET nation.N_NAME = line.N_NAME, '
            'nation.N_REGIONKEY = line.N_REGIONKEY, '
            'nation.N_COMMENT = line.N_COMMENT; ')
예제 #14
0
    def delete_node(self):
        graphDB = InitilizeDB.init()


       # [LINEITEM | CUSTOMER | NATION | REGION | SUPPLIER | ORDER | PARTSUPP | PART]

        for i in range(5):
            graphDB.run(
                "MATCH (n) "
                "WITH n LIMIT 100000 "
                "DETACH DELETE n; "

            )

        # graphDB.run(
        #     'MATCH (n:LINEITEM) WITH n LIMIT 400000 DETACH DELETE n; '
        # )
        print("Deleted node")
예제 #15
0
    def insert_nodes_part(self):
        graphDB = InitilizeDB.init()
        try:
            graphDB.run('CREATE CONSTRAINT ON (p:PART) ASSERT p.id IS UNIQUE;')
        except:
            pass

        graphDB.run('USING PERIODIC COMMIT '
                    'LOAD CSV WITH HEADERS '
                    'FROM "file:///part.csv" AS line FIELDTERMINATOR "|" '
                    'CREATE (part:PART { id: TOINTEGER(line.P_PARTKEY) }) '
                    'SET part.P_NAME =line.P_NAME,'
                    '    part.P_MFGR = line.P_MFGR,'
                    '    part.P_BRAND = line.P_BRAND,'
                    '    part.P_TYPE = line.P_TYPE,'
                    '    part.P_SIZE = TOINTEGER(line.P_SIZE),'
                    '    part.P_CONTAINER = line.P_CONTAINER,'
                    '    part.P_RETAILPRICE =TOFLOAT( line.P_RETAILPRICE),'
                    '    part.P_COMMENT = line.P_COMMENT; ')
예제 #16
0
    def insert_nodes_customer(self):
        graphDB = InitilizeDB.init()

        # graphDB.run(
        #     'CREATE CONSTRAINT ON (c:CUSTOMER) ASSERT c.id IS UNIQUE;'
        # )

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS '
            'FROM "file:///customer.csv" AS line FIELDTERMINATOR "|" '
            'CREATE (customer:CUSTOMER { id: TOINTEGER(line.C_CUSTKEY) } ) '
            'SET customer.C_NAME = line.C_NAME, '
            'customer.C_ADDRESS = line.C_ADDRESS, '
            'customer.C_NATIONKEY = line.C_NATIONKEY, '
            'customer.C_PHONE = line.C_PHONE, '
            'customer.C_ACCTBAL = toFloat(line.C_ACCTBAL), '
            'customer.C_MKTSEGMENT = line.C_MKTSEGMENT, '
            'customer.C_COMMENT = line.C_COMMENT; ')
예제 #17
0
    def insert_nodes_supplier(self):
        graphDB = InitilizeDB.init()
        try:
            graphDB.run(
                'CREATE CONSTRAINT ON (s:SUPPLIER) ASSERT s.id IS UNIQUE; ')
        except:
            pass

        graphDB.run(
            'USING PERIODIC COMMIT '
            'LOAD CSV WITH HEADERS  '
            'FROM "file:///supplier.csv" AS line FIELDTERMINATOR "|" '
            'CREATE (supplier:SUPPLIER { id: TOINTEGER(line.S_SUPPKEY) }) '
            'SET supplier.S_NAME = line.S_NAME, '
            '	supplier.S_ADDRESS = line.S_ADDRESS, '
            '    supplier.S_NATIONKEY = line.S_NATIONKEY, '
            '    supplier.S_PHONE = line.S_PHONE, '
            '    supplier.S_ACCTBAL = line.S_ACCTBAL, '
            '    supplier.S_COMMENT = line.S_COMMENT; ')
예제 #18
0
    def execute(self):
        try:
            graphDB = InitilizeDB.init()
            start_time = time.time()

            result = graphDB.run(
                "WITH date('1998-12-01') - duration('P90D') AS my_date "
                "MATCH (item:LINEITEM) "
                "WHERE date(item.L_SHIPDATE) <= date(my_date) "
                "RETURN item.L_RETURNFLAG,item.L_LINESTATUS,sum(item.L_QUANTITY) AS sum_qty, sum(item.L_EXTENDEDPRICE) AS sum_base_price, sum(item.L_EXTENDEDPRICE*(1-item.L_DISCOUNT)) AS sum_disc_price,sum(item.L_EXTENDEDPRICE*(1-item.L_DISCOUNT)*(1+item.L_TAX)) AS sum_charge,avg(item.L_QUANTITY) AS avg_qty, avg(item.L_EXTENDEDPRICE) AS avg_price, avg(item.L_DISCOUNT) AS avg_disc, COUNT(*) AS count_order "
                "ORDER BY item.L_RETURNFLAG, item.L_LINESTATUS; ")
            print(result)
            end_time = time.time()
            print("---------------Query 1-------------")
            print("Start time: " + str(start_time))
            print("End time: " + str(end_time))
            print("In seconds: " + str("{:.7f}".format(end_time - start_time)))
        except:
            print("py2neo ERROR:")
예제 #19
0
    def execute(self):
        try:
            graphDB = InitilizeDB.init()
            start_time = time.time()

            result = graphDB.run(
                "MATCH (lineitem:LINEITEM)-[:BELONGS_TO_7]->(order:ORDER)-[:BY_5]->(customer:CUSTOMER) "
                "WHERE customer.C_MKTSEGMENT = 'BUILDING' AND date(order.O_ORDERDATE) < date('1995-03-15') AND date(lineitem.L_SHIPDATE) > date('1995-03-15') "
                "RETURN order.id, sum(lineitem.L_EXTENDEDPRICE*(1-lineitem.L_DISCOUNT)) AS REVENUE, order.O_ORDERDATE, order.O_SHIPPRIORITY "
                "ORDER BY REVENUE DESC, order.O_ORDERDATE "
                "LIMIT 10; ")

            print(result)
            end_time = time.time()
            print("---------------Query 3-------------")
            print("Start time: " + str(start_time))
            print("End time: " + str(end_time))
            print("In seconds: " + str("{:.7f}".format(end_time - start_time)))
        except:
            print("py2neo ERROR:")
예제 #20
0
    def execute(self):
        try:
            graphDB = InitilizeDB.init()
            start_time = time.time()

            result = graphDB.run(
                "WITH date('1993-07-01') + duration('P3M') AS my_date "
                "MATCH (lineitem: LINEITEM)-[:BELONGS_TO_7]->(order: ORDER) "
                "WHERE  date(lineitem.L_COMMITDATE) < date(lineitem.L_RECEIPTDATE) AND date(order.O_ORDERDATE) >= date('1993-07-01') AND date(order.O_ORDERDATE) < date(my_date) "
                "RETURN order.O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT "
                "ORDER BY order.O_ORDERPRIORITY; ")

            print(result)
            end_time = time.time()
            print("---------------Query 4-------------")
            print("Start time: " + str(start_time))
            print("End time: " + str(end_time))
            print("In seconds: " + str("{:.7f}".format(end_time - start_time)))
        except:
            print("py2neo ERROR:")
예제 #21
0
    def execute(self):
        try:
            graphDB = InitilizeDB.init()
            start_time = time.time()

            result = graphDB.run(
                "WITH date('1994-01-01') + duration('P1Y') AS my_date "
                "MATCH (lineitem: LINEITEM)-[:BELONGS_TO_7]->(order: ORDER)-[:BY_5]->(customer: CUSTOMER)-[:FROM_4]->(nation: NATION)-[:FROM_10]-(region: REGION) "
                "WHERE region.R_NAME = 'ASIA' AND date(order.O_ORDERDATE) >= date('1994-01-01') AND date(order.O_ORDERDATE) < date(my_date) "
                "RETURN nation.N_NAME, SUM(lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT)) AS REVENUE "
                "ORDER BY REVENUE DESC; ")

            print(result)
            end_time = time.time()
            print("---------------Query 5-------------")
            print("Start time: " + str(start_time))
            print("End time: " + str(end_time))
            print("In seconds: " + str("{:.7f}".format(end_time - start_time)))
        except:
            print("py2neo ERROR:")
예제 #22
0
def main():
    print("Neo4j db started...")
    db = InitilizeDB.init()

    insert_data = False
    if insert_data:
        insert_task = InsertData(db)
        insert_task.insert_nodes()
        insert_task.insert_relations()

    delete_data = False
    if delete_data:
        delete_task = DeleteData(db)
        delete_task.delete_nodes()

    run_queries = True
    if run_queries:
        query = RunQueries()
        query.run_queries()

    print("Successful!")
예제 #23
0
    def execute(self):
        try:
            graphDB = InitilizeDB.init()
            start_time = time.time()

            result = graphDB.run(
                "MATCH (ps:PARTSUPP)-[:SUPPLIED_BY_3]->(s:SUPPLIER)-[:BELONGS_TO_1]->(n:NATION)-[:FROM_10]->(r:REGION) "
                "where r.R_NAME = 'EUROPE' with ps, min(ps.PS_SUPPLYCOST) as minvalue "
                "MATCH (ps:PARTSUPP)-[:COMPOSED_BY_2]->(p:PART) "
                "MATCH (ps:PARTSUPP)-[:SUPPLIED_BY_3]->(s:SUPPLIER)-[:BELONGS_TO_1]->(n:NATION)-[:FROM_10]->(r:REGION) "
                "where p.P_SIZE = 15 and p.P_TYPE =~ '.*BRASS.*' and r.R_NAME = 'EUROPE' and ps.PS_SUPPLYCOST = minvalue "
                "return p.id,p.P_MFGR,s.S_ACCTBAL,s.S_NAME,s.S_ADDRESS,s.S_PHONE,s.S_COMMENT,n.N_NAME, r.R_NAME "
                "order by s.S_ACCTBAL desc,n.N_NAME,s.S_NAME,p.id; ")

            print(result)
            end_time = time.time()
            print("---------------Query 2-------------")
            print("Start time: " + str(start_time))
            print("End time: " + str(end_time))
            print("In seconds: " + str("{:.7f}".format(end_time - start_time)))
        except:
            print("py2neo ERROR:")
예제 #24
0
 def insert_nodes_lineItem(self):
     graphDB = InitilizeDB.init()
     graphDB.run(
         'USING PERIODIC COMMIT '
         'LOAD CSV WITH HEADERS '
         'FROM "file:///lineitem_neo4j.csv" AS line FIELDTERMINATOR "|" '
         'CREATE (lineItem:LINEITEM) '
         'SET lineItem.L_ORDERKEY = toInteger(line.L_ORDERKEY), '
         '    lineItem.L_PARTKEY = toInteger(line.L_PARTKEY),	'
         '    lineItem.L_SUPPKEY = toInteger(line.L_SUPPKEY), '
         '    lineItem.L_LINENUMBER = toInteger(line.L_LINENUMBER), '
         '    lineItem.L_QUANTITY = toFloat(line.L_QUANTITY), '
         '    lineItem.L_EXTENDEDPRICE = toFloat(line.L_EXTENDEDPRICE),'
         '    lineItem.L_DISCOUNT = toFloat(line.L_DISCOUNT),'
         '    lineItem.L_TAX = toFloat(line.L_TAX),'
         '    lineItem.L_RETURNFLAG = line.L_RETURNFLAG,'
         '    lineItem.L_LINESTATUS = line.L_LINESTATUS,'
         '    lineItem.L_SHIPDATE = line.L_SHIPDATE,'
         '    lineItem.L_COMMITDATE = line.L_COMMITDATE,'
         '    lineItem.L_RECEIPTDATE = line.L_RECEIPTDATE,'
         '    lineItem.L_SHIPINSTRUCT = line.L_SHIPINSTRUCT,'
         '    lineItem.L_SHIPMODE = line.L_SHIPMODE,'
         '    lineItem.L_COMMENT = line.L_COMMENT, '
         '    lineItem.L_SHIPDATE_DAY = TOINTEGER(line.L_SHIPDATE_DAY),'
         '    lineItem.L_SHIPDATE_MONTH = TOINTEGER(line.L_SHIPDATE_MONTH),'
         '    lineItem.L_SHIPDATE_YEAR = TOINTEGER(line.L_SHIPDATE_YEAR),'
         '    lineItem.L_COMMITDATE_DAY = TOINTEGER(line.L_COMMITDATE_DAY),'
         '    lineItem.L_COMMITDATE_MONTH = TOINTEGER(line.L_COMMITDATE_MONTH),'
         '    lineItem.L_COMMITDATE_YEAR =TOINTEGER(line.L_COMMITDATE_YEAR),'
         '    lineItem.L_RECEIPTDATE_DAY = TOINTEGER(line.L_RECEIPTDATE_DAY),'
         '    lineItem.L_RECEIPTDATE_MONTH = TOINTEGER(line.L_RECEIPTDATE_MONTH),'
         '    lineItem.L_RECEIPTDATE_YEAR = TOINTEGER(line.L_RECEIPTDATE_YEAR);'
     )
     graphDB.run('CREATE INDEX ON :LINEITEM(L_SUPPKEY); ')
     graphDB.run('CREATE INDEX ON :LINEITEM(L_ORDERKEY); ')
     graphDB.run('CREATE INDEX ON :LINEITEM(L_PARTKEY); ')
     graphDB.run('CREATE INDEX ON :LINEITEM(L_LINENUMBER); ')