def init_postgres(config, graph_name, index): """ Initialize the RDF graph GRAPH_NAME with a PostgreSQL/PostgreSQL-MVCC backend, described in the configuration file CONFIG. """ # install logger coloredlogs.install(level='INFO', fmt='%(asctime)s - %(levelname)s %(message)s') logger = logging.getLogger(__name__) # load graph from config file graph, kind = load_graph(config, graph_name, logger, backends=['postgres', 'postgres-mvcc']) enable_mvcc = kind == 'postgres-mvcc' # init postgre connection connection = connect_postgres(graph) if connection is None: exit(1) # turn off autocommit connection.autocommit = False # create all SQL queries used to init the graph, using the graph name table_name = graph['name'] create_table_query = p_utils.get_postgres_create_table( table_name, enable_mvcc=enable_mvcc) create_indexes_queries = p_utils.get_postgres_create_indexes( table_name, enable_mvcc=enable_mvcc) cursor = connection.cursor() # create the main SQL table logger.info("Creating SQL table {}...".format(table_name)) cursor.execute(create_table_query) logger.info("SPARQL table {} successfully created".format(table_name)) # create the additional inexes on OSP and POS if index: logger.info("Creating additional B-tree indexes...") for q in create_indexes_queries: cursor.execute(q) logger.info("Additional B-tree indexes successfully created") else: logger.info("Skipping additional indexes creation on user-demand") # commit and cleanup connection logger.info("Committing and cleaning up...") connection.commit() cursor.close() connection.close() logger.info( "Sage PostgreSQL model for table {} successfully initialized".format( table_name))
def init_postgres(config, graph_name, index): """Initialize the RDF graph GRAPH_NAME with a PostgreSQL backend, described in the configuration file CONFIG.""" # load graph from config file graph, backend = load_graph( config, graph_name, logger, backends=['postgres', 'postgres-mvcc', 'postgres-catalog']) # init postgre connection logger.info("Connecting to the PostgreSQL server...") connection = connect_postgres(graph) if connection is None: logger.error('Failed to establish a connection with PostgreSQL') exit(1) logger.info("Connected to the PostgreSQL server") # turn off autocommit connection.autocommit = False # create a cursor to interact with the database cursor = connection.cursor() # create the main SQL tables logger.info("Creating PostgreSQL tables...") create_table_queries = psql_utils.get_create_tables_queries( graph_name, backend) for query in create_table_queries: cursor.execute(query) logger.info("PostgreSQL tables successfully created") # create the additional indexes on OSP and POS if index: logger.info("Creating additional B-tree indexes...") create_indexes_queries = psql_utils.get_create_indexes_queries( graph_name, backend) for query in create_indexes_queries: cursor.execute(query) logger.info("Additional B-tree indexes successfully created") else: logger.info("Skipping additional indexes creation on user-demand") # commit and cleanup connection logger.info("Committing and cleaning up...") connection.commit() cursor.close() connection.close() logger.info( f"Sage PostgreSQL model for graph '{graph_name}' successfully initialized" )
def init_sqlite(config, graph_name, index): """Initialize the RDF graph GRAPH_NAME with a SQlite backend, described in the configuration file CONFIG.""" # load graph from config file graph, backend = load_graph(config, graph_name, logger, backends=['sqlite', 'sqlite-catalog']) # init SQlite connection logger.info("Connecting to the SQlite server...") connection = connect_sqlite(graph) connection.isolation_level = None if connection is None: logger.error('Failed to establish a connection with SQlite') exit(1) logger.info("Connected to the SQlite server") # create a cursor to interact with the database cursor = connection.cursor() # start a transaction cursor.execute("BEGIN TRANSACTION") # create the main SQL tables logger.info("Creating SQlite tables...") create_table_queries = sqlite_utils.get_create_tables_queries( graph_name, backend) for query in create_table_queries: cursor.execute(query) logger.info("SQlite tables successfully created") # create the additional indexes on OSP and POS if index: logger.info("Creating additional B-tree indexes...") create_indexes_queries = sqlite_utils.get_create_indexes_queries( graph_name, backend) for query in create_indexes_queries: cursor.execute(query) logger.info("Additional B-tree indexes successfully created") else: logger.info("Skipping additional indexes creation on user-demand") # commit and cleanup connection logger.info("Committing and cleaning up...") cursor.execute("COMMIT") cursor.close() connection.close() logger.info( f"Sage SQlite model for graph '{graph_name}' successfully initialized")
def index_postgres(config, graph_name): """Create the additional B-tree indexes on the RDF graph GRAPH_NAME, described in the configuration file CONFIG.""" # load graph from config file graph, backend = load_graph( config, graph_name, logger, backends=['postgres', 'postgres-mvcc', 'postgres-catalog']) # init PostgreSQL connection logger.info("Connecting to the PostgreSQL server...") connection = connect_postgres(graph) if connection is None: logger.error('Failed to establish a connection with PostgreSQL') exit(1) logger.info("Connected to the PostgreSQL server") # turn off autocommit connection.autocommit = False # create a cursor to interact with the database cursor = connection.cursor() # create indexes start = time.time() logger.info("Creating additional B-tree indexes...") create_indexes_queries = psql_utils.get_create_indexes_queries( graph_name, backend) for query in create_indexes_queries: cursor.execute(query) stop = time.time() logger.info( f"Additional B-tree indexes successfully created in {stop - start}s") # rebuild table statistics logger.info("Rebuilding table statistics...") start = time.time() cursor.execute(psql_utils.get_analyze_query(graph_name)) logger.info( f"Table statistics successfully rebuilt in {time.time() - start}s") # commit and cleanup connection logger.info("Committing and cleaning up...") connection.commit() cursor.close() connection.close() logger.info( f"Sage PostgreSQL model for graph '{graph_name}' successfully initialized" )
def index_sqlite(config, graph_name): """Create the additional B-tree indexes on the RDF graph GRAPH_NAME, described in the configuration file CONFIG.""" # load graph from config file graph, backend = load_graph(config, graph_name, logger, backends=['sqlite', 'sqlite-catalog']) # init SQlite connection logger.info("Connecting to the SQlite server...") connection = connect_sqlite(graph) connection.isolation_level = None if connection is None: logger.error('Failed to establish a connection with SQlite') exit(1) logger.info("Connected to the SQlite server") # create a cursor to interact with the database cursor = connection.cursor() # start a transaction cursor.execute("BEGIN TRANSACTION") # create indexes start = time.time() logger.info("Creating additional B-tree indexes...") create_indexes_queries = sqlite_utils.get_create_indexes_queries( graph_name, backend) for query in create_indexes_queries: cursor.execute(query) stop = time.time() logger.info( f"Additional B-tree indexes successfully created in {stop - start}s") # rebuild table statistics logger.info("Rebuilding table statistics...") start = time.time() cursor.execute(sqlite_utils.get_analyze_query(graph_name)) logger.info( f"Table statistics successfully rebuilt in {time.time() - start}s") # commit and cleanup connection logger.info("Committing and cleaning up...") cursor.execute("COMMIT") cursor.close() connection.close() logger.info( f"Sage SQlite model for graph '{graph_name}' successfully initialized")
def index_postgres(config, graph_name): """ Create the additional B-tree indexes on the RDF graph GRAPH_NAME, described in the configuration file CONFIG. The graph must use the PostgreSQL or PostgreSQL-MVCC backend. """ # install logger coloredlogs.install(level='INFO', fmt='%(asctime)s - %(levelname)s %(message)s') logger = logging.getLogger(__name__) # load graph from config file graph, kind = load_graph(config, graph_name, logger, backends=['postgres', 'postgres-mvcc']) enable_mvcc = kind == 'postgres-mvcc' # init PostgreSQL connection connection = connect_postgres(graph) if connection is None: exit(1) # turn off autocommit connection.autocommit = False # create all SQL queries used to init the graph, using the graph name table_name = graph['name'] create_indexes_queries = p_utils.get_postgres_create_indexes( table_name, enable_mvcc=enable_mvcc) # create indexes cursor = connection.cursor() start = time() logger.info("Creating additional B-tree indexes...") for q in create_indexes_queries: cursor.execute(q) stop = time() logger.info( "Additional B-tree indexes successfully created in {}s".format(stop - start)) # commit and cleanup connection logger.info("Committing and cleaning up...") connection.commit() cursor.close() connection.close() logger.info( "Sage PostgreSQL model for table {} successfully initialized".format( table_name))
def stream_postgres(config, graph_name, rdf_file, block_size, commit_threshold): """ Insert RDF triples from file RDF_FILE into the RDF graph GRAPH_NAME, described in the configuration file CONFIG. The graph must use the PostgreSQL or PostgreSQL-MVCC backend. """ # load graph from config file graph, kind = load_graph(config, graph_name, logger, backends=['postgres', 'postgres-mvcc']) enable_mvcc = kind == 'postgres-mvcc' # init PostgreSQL connection logger.info("Connecting to PostgreSQL server...") connection = connect_postgres(graph) logger.info("Connected to PostgreSQL server") if connection is None: exit(1) # turn off autocommit connection.autocommit = False # compute SQL table name and the bulk load SQL query table_name = graph['name'] insert_into_query = p_utils.get_postgres_insert_into( table_name, enable_mvcc=enable_mvcc) cursor = connection.cursor() logger.info("Reading NT RDF source file...(I hope)") # iterator, nb_triples = get_rdf_reader(rdf_file, format=format) start = time() bucket = list() n = SkipParser( StreamSink(bucket, block_size, insert_into_query, cursor, connection, commit_threshold, logger)) with open(rdf_file, "rb") as anons: n.skipparse(anons) try: #print("remaining bucket:"+str(bucket)) execute_values(cursor, insert_into_query, bucket, page_size=block_size) except: logger.error("Failed to insert:" + str(bucket)) connection.commit() end = time() logger.info( "RDF triples ingestion successfully completed in {}s".format(end - start)) # run an ANALYZE query to rebuild statistics # logger.info("Rebuilding table statistics...") # start = time() # cursor.execute("ANALYZE {}".format(table_name)) # end = time() # logger.info("Table statistics successfully rebuilt in {}s".format(end - start)) # commit and cleanup connection logger.info("Committing and cleaning up...") connection.commit() cursor.close() connection.close() logger.info( "RDF data from file '{}' successfully inserted into RDF graph '{}'". format(rdf_file, table_name)) logger.info(f"Remember to run ANALYZE on Postgres table: {table_name}")
def put_postgres(config, graph_name, rdf_file, format, block_size, commit_threshold): """ Insert RDF triples from file RDF_FILE into the RDF graph GRAPH_NAME, described in the configuration file CONFIG. The graph must use the PostgreSQL or PostgreSQL-MVCC backend. """ # install logger coloredlogs.install(level='INFO', fmt='%(asctime)s - %(levelname)s %(message)s') logger = logging.getLogger(__name__) # load graph from config file graph, kind = load_graph(config, graph_name, logger, backends=['postgres', 'postgres-mvcc']) enable_mvcc = kind == 'postgres-mvcc' # init PostgreSQL connection logger.info("Connecting to PostgreSQL server...") connection = connect_postgres(graph) logger.info("Connected to PostgreSQL server") if connection is None: exit(1) # turn off autocommit connection.autocommit = False # compute SQL table name and the bulk load SQL query table_name = graph['name'] insert_into_query = p_utils.get_postgres_insert_into( table_name, enable_mvcc=enable_mvcc) logger.info("Reading RDF source file...") iterator, nb_triples = get_rdf_reader(rdf_file, format=format) logger.info( "RDF source file loaded. Found ~{} RDF triples to ingest.".format( nb_triples)) logger.info("Starting RDF triples ingestion...") cursor = connection.cursor() # insert rdf triples start = time() to_commit = 0 # insert by bucket (and show a progress bar) with click.progressbar( length=nb_triples, label="Inserting RDF triples".format(nb_triples)) as bar: for bucket in bucketify(iterator, block_size): to_commit += len(bucket) # bulk load the bucket of RDF triples, then update progress bar execute_values(cursor, insert_into_query, bucket, page_size=block_size) bar.update(len(bucket)) # commit if above threshold if to_commit >= commit_threshold: # logger.info("Commit threshold reached. Committing all changes...") connection.commit() # logger.info("All changes were successfully committed.") to_commit = 0 end = time() logger.info( "RDF triples ingestion successfully completed in {}s".format(end - start)) # run an ANALYZE query to rebuild statistics logger.info("Rebuilding table statistics...") start = time() cursor.execute("ANALYZE {}".format(table_name)) end = time() logger.info("Table statistics successfully rebuilt in {}s".format(end - start)) # commit and cleanup connection logger.info("Committing and cleaning up...") connection.commit() cursor.close() connection.close() logger.info( "RDF data from file '{}' successfully inserted into RDF graph '{}'". format(rdf_file, table_name))
def put_sqlite(config, graph_name, rdf_file, format, block_size, commit_threshold, cache_size): """Insert RDF triples from file RDF_FILE into the RDF graph GRAPH_NAME, described in the configuration file CONFIG.""" # load graph from config file graph, backend = load_graph(config, graph_name, logger, backends=['sqlite', 'sqlite-catalog']) # init SQlite connection logger.info("Connecting to the SQlite server...") connection = connect_sqlite(graph) connection.isolation_level = None if connection is None: logger.error('Failed to establish a connection with SQlite') exit(1) logger.info("Connected to the SQlite server") # create a cursor to interact with the database cursor = connection.cursor() # start a transaction cursor.execute("BEGIN TRANSACTION") logger.info("Reading RDF source file...") nb_triples = get_nb_triples(rdf_file, format) logger.info(f"Found ~{nb_triples} RDF triples to ingest.") start = time.time() to_commit = 0 inserted = 0 dropped = 0 cache = pylru.lrucache(cache_size) with click.progressbar( length=nb_triples, label= f"Inserting RDF triples 0/{nb_triples} - {dropped} triples dropped." ) as bar: def on_bucket(bucket): nonlocal to_commit, inserted, dropped insert_bucket(cursor, bucket, graph_name, backend, block_size, cache) to_commit = to_commit + len(bucket) if to_commit >= commit_threshold: connection.commit() to_commit = 0 inserted = inserted + len(bucket) bar.label = f"Inserting RDF triples {inserted}/{nb_triples} - {dropped} triples dropped." bar.update(len(bucket)) def on_error(error): nonlocal dropped, inserted dropped = dropped + 1 bar.label = f"Inserting RDF triples {inserted}/{nb_triples} - {dropped} triples dropped." bar.update(0) def on_complete(): nonlocal start logger.info( f"Triples ingestion successfully completed in {time.time() - start}s" ) logger.info("Rebuilding table statistics...") start = time.time() cursor.execute(sqlite_utils.get_analyze_query(graph_name)) logger.info( f"Table statistics successfully rebuilt in {time.time() - start}s" ) logger.info("Committing and cleaning up...") cursor.execute("COMMIT") cursor.close() connection.close() logger.info( f"RDF data from file '{rdf_file}' successfully inserted into RDF graph '{graph_name}'" ) logger.info("Starting RDF triples ingestion...") parser = ParserFactory.create_parser(format, block_size) parser.on_bucket = on_bucket parser.on_error = on_error parser.on_complete = on_complete parser.parsefile(rdf_file)