<jupyter_start><jupyter_text>GSQL101 - on a Jupyter Notebook  Setting up the graph  Write data files to disk<jupyter_code>%%writefile person.csv
name,gender,age,state
Tom,male,40,ca
Dan,male,34,ny
Jenny,female,25,tx
Kevin,male,28,az
Amily,female,22,ca
Nancy,female,20,ky
Jack,male,26,fl
%%writefile friendship.csv
person1,person2,date
Tom,Dan,2017-06-03
Tom,Jenny,2015-01-01
Dan,Jenny,2016-08-03
Jenny,Amily,2015-06-08
Dan,Nancy,2016-01-03
Nancy,Jack,2017-03-02
Dan,Kevin,2015-12-30<jupyter_output>Overwriting friendship.csv<jupyter_text>Define a function to send and process the output of GSQL queries<jupyter_code>import subprocess, json

def gsql(query, options='-g social'):
    
    comp = subprocess.run(['gsql', options, query], text=True, capture_output=True)
    
    try:
        json_object = json.loads(comp.stdout)
    except:
        print(comp.stdout.replace('\\n','\n'))
        return
    else:
        return json_object<jupyter_output><empty_output><jupyter_text>Clear the graph<jupyter_code>gsql('drop all', options='')<jupyter_output>Dropping all, about 1 minute ...
Abort all active loading jobs
Try to abort all loading jobs on graph social, it may take a while ...
[ABORT_SUCCESS] No active Loading Job to abort.

Shutdown restpp gse gpe ...
Graph store /home/tigergraph/tigergraph/gstore/0/ has been cleared!
Everything is dropped.<jupyter_text>Create the graph schemaSee [GSQL 101 - Module 1: Graph Schema](https://youtu.be/him2Uy3Nn7Y)<jupyter_code>from IPython.display import IFrame

IFrame(width=560, height=315, src='https://www.youtube.com/embed/him2Uy3Nn7Y')
gsql('''
create vertex person (primary_id name string, name string, age int, 
                      gender string, state string)
                      
create undirected edge friendship (from person, to person, 
                                   connect_day datetime)

create graph social (person, friendship)
''', options='')<jupyter_output>The vertex type person is created.
The edge type friendship is created.

Restarting gse gpe restpp ...

Finish restarting services in 28.631 seconds!
The graph social is created.<jupyter_text>Create data loading job and run itSee [GSQL 101 - Module 2: Data Loading](https://youtu.be/IFfR3tQNhoA)<jupyter_code>IFrame(width=560, height=315, src='https://www.youtube.com/embed/IFfR3tQNhoA')
gsql('''
create loading job load_social for graph social {
    define filename file1="person.csv";
    define filename file2="friendship.csv";
    
    load file1 to vertex person values ($"name", $"name", $"age", $"gender", $"state") 
       using header="true", separator=",";
    
    load file2 to edge friendship values ($"person1", $"person2", $"date") 
       using header="true", separator=",";   
}

run loading job load_social
''')<jupyter_output>The job load_social is created.
[Tip: Use "CTRL + C" to stop displaying the loading status update, then use "SHOW LOADING STATUS jobid" to track the loading progress again]
[Tip: Manage loading jobs with "ABORT/RESUME LOADING JOB jobid"]
Starting the following job, i.e.
  JobName: load_social, jobid: social.load_social.file.m1.1592359900330
  Loading log: '/home/tigergraph/tigergraph/logs/restpp/restpp_loader_logs/social/social.load_social.file.m1.1592359900330.log'

Job "social.load_social.file.m1.1592359900330" loading status
[RUNNING] m1 ( Finished: 0 / Total: 2 )
[2A[2KJob "social.load_social.file.m1.1592359900330" loading status
[2K[RUNNING] m1 ( Finished: 2 / Total: 2 )
  [LOADED]
  +---------------------------------------------------------------------------+
  |                       FILENAME |   LOADED LINES |   AVG SPEED |   DURATION|
  |    /home/tigergraph/person.csv |              8 |       7 l/s |     1.00 s|
  |/home/tigergraph/friendship.csv |              8 |       7[...]<jupyter_text>Built-in `SELECT` queriesSee [GSQL 101 - Module 3: Graph Inspection Language](https://youtu.be/akGOSEWMC8I)<jupyter_code>IFrame(width=560, height=315, src='https://www.youtube.com/embed/akGOSEWMC8I')<jupyter_output><empty_output><jupyter_text>`SELECT` queries return a native Python structure<jupyter_code>q = gsql('select count(*) from person')
q
q[0]['count']<jupyter_output><empty_output><jupyter_text>Since this is GSQL the `FROM` argument can be a pattern<jupyter_code>gsql('select count() from person-(friendship)-person')<jupyter_output><empty_output><jupyter_text>The `WHERE` arument is a filter on the `FROM` pattern<jupyter_code>gsql('select * from person where primary_id=="Tom"')
q = gsql('select * from person where gender=="female"')
q<jupyter_output><empty_output><jupyter_text>Use Python's list comprehension to acess results<jupyter_code>[v['attributes']['age'] for v in q]<jupyter_output><empty_output><jupyter_text>Transform into a Pandas DataFrame<jupyter_code>import pandas as pd

pd.DataFrame([v['attributes'] for v in q])<jupyter_output><empty_output><jupyter_text>User defined queriesSee [GSQL 101 - Module 4: Basic GSQL](https://youtu.be/eK6f7qnylj0)<jupyter_code>IFrame(width=560, height=315, src='https://www.youtube.com/embed/eK6f7qnylj0')<jupyter_output><empty_output><jupyter_text>Queries can saved and compiled<jupyter_code>gsql('''
create query hello(vertex<person> p) for graph social{
    
    start = {p};
    tgt = select t from start:s-(friendship:e)-person:t ;
    print tgt;
}

install query hello
''')<jupyter_output>The query hello has been added!
Start installing queries, about 1 minute ...
hello query: curl -X GET 'http://127.0.0.1:9000/query/social/hello?p=VALUE'. Add -H "Authorization: Bearer TOKEN" if authentication is enabled.


[                                                                 ] 0% (0/1)   
[                                                                 ] 0% (0/1)   
[===============================                                  ] 47% (0/1)  
[==============================================================   ] 95% (0/1)  
[=================================================================] 100% (1/1)<jupyter_text>Then run the query and process the output<jupyter_code>q = gsql('run query hello("Tom")')
q
[v['attributes']['age'] for v in q['results'][0]['tgt']]<jupyter_output><empty_output><jupyter_text>AccumulatorsSee [GSQL 101 - Module 5: Advanced GSQL (Accumulators)](https://youtu.be/ysrm9OjVoqg)<jupyter_code>IFrame(width=560, height=315, src='https://www.youtube.com/embed/ysrm9OjVoqg')<jupyter_output><empty_output><jupyter_text>Measuring hop distance requires that we flag every vertex we visit using a local accumulator `@visited`<jupyter_code>gsql('''
create query hello2(vertex<person> p) for graph social {
    
    OrAccum @visited = false;
    AvgAccum @@aveAge;
    
    start = {p};
    
    firstHop = select t from start:s-(friendship:e)-person:t
               accum t.@visited += true, s.@visited += true;
    
    secondHop = select t from firstHop:s-(friendship:e)-person:t
                where t.@visited == false
                post_accum @@aveAge += t.age;
    
    print secondHop;
    print @@aveAge;
            
}

install query hello2
''')<jupyter_output>The query hello2 has been added!
Start installing queries, about 1 minute ...
hello2 query: curl -X GET 'http://127.0.0.1:9000/query/social/hello2?p=VALUE'. Add -H "Authorization: Bearer TOKEN" if authentication is enabled.


[                                                                 ] 0% (0/1)   
[                                                                 ] 0% (0/1)   
[===============================                                  ] 47% (0/1)  
[==============================================================   ] 95% (0/1)  
[=================================================================] 100% (1/1)<jupyter_text>Results exclude Jenny and Dan even though they are a friend of a friend of Tom because they are also direct friends of Tom<jupyter_code>q = gsql('run query hello2("Tom")')
q<jupyter_output><empty_output><jupyter_text>We can verify that the global accumulator `@@aveAge` did its job correctly<jupyter_code>q['results'][1]['@@aveAge']
ages = [v['attributes']['age'] for v in q['results'][0]['secondHop']]
ages
sum(ages)/len(ages)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16836172037740474, 'fraction_numerical': 0.03651513294939346, 'mean_word_length': 3.6296086216676122, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 69, 'https://': 10, 'lorem ipsum': 0, 'www.': 5, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '657171', 'license': 'Apache-2.0', 'n_tokens_mistral': 2868, 'n_tokens_neox': 2620, 'n_words': 641, 'path': 'demos/guru_scripts/docker_notebook/gsql101.ipynb', 'repo_name': 'carlboudreau007/ecosys', 'size': '8161'}