alialkadhim/pascal repository overview

⁠Pascal docker Image README

Pascal=Python Analysis and SQL Computation and Logic.

⁠This image is intended to be used with https://github.com/AliAlkadhim/PASCAL⁠

⁠If you are interested in the command line (CLI) version for CERN, go to https://gitlab.cern.ch/aalkadhi/pascal⁠

If you are unfamiliar with docker see my Docker_Tutorials_V2.pdf (also if you want Dockerfile_tutorials_V1.pdf, docker_run_tutorials_V1.pdf. This assumes you don't have a mac with the recent M1 chip (blame Apple)

⁠Run a tutorial jupyter notebook interactively

Step 1: Pull the image

docker pull alialkadhim/pascal

Step 2: Run the image in an interactive container:

sudo docker run --net=host -e DISPLAY=$DISPLAY --volume="$HOME/.Xauthority:/root/.Xauthority:rw" --volume="/tmp/.X11-unix:/tmp/.X11-unix" -p 8888:8888 -it alialkadhim/pascal /bin/bash

Step 3: Now you're inside the docker container. Your working directory is /home/: See what's there! Establish an ssh tunnel to lxplus: do the follwoing inside the docker container *Replace aalkadhi to your lxplus username in /home/tunnel.sh and do bash tunnel.sh

#!/bin/bash
lsof -ti:10131 | xargs kill -9
ssh -4 -fNL 10131:itrac1609-v.cern.ch:10121 -L 10132:itrac1601-v.cern.ch:10121 [email protected]

Of course, type "yes" if asked "are you sure?" and enter your CERN account (lxplus) password.

Step 4: Inside the docker container, start a jupyterlab session using the same port forwarding (8888 in this case):

jupyter-lab --ip 0.0.0.0 --port 8888 --allow-root &

Step 5: Now in your host (local) machine, go to the url that is given to you in the docker terminal. It will say something like Or copy and paste one of these URLs:. If you have some other version of jupyterlab on your local machine, you might have to go to localhost:8888 in a browser and type the password from the docker container that is shown after ?="". Note: some other versions of jupyterlab use token instead of password, the token will be after lab?token= once you start jupyter-lab.

Step 6: Follow the HGCAL DB Python Query Tutorial in by double-clicking Pascal_jupyter_tutrial_1.ipynb in the left tab.

⁠Jupyter notebook Ideas

⁠Interactive Jupyter notebook for DB Interaction/analysis

We will be using the python module cx_Oracle to connect and execute SQL queries for the HGCAL INT2R database. The documentation of cx_Oracle is here⁠, but I took care of the installation/configuration, etc, so that you focus on the simplest ways that you can interact with the database.

⁠Extra recommended (optional) parameters for docker run command.

Put this in a file like RUN_dca_int2e-linux-gui.sh so that you don't forget these commands

#!/bin/bash
sudo docker run \
--rm \
--net=host \
-e DISPLAY=$DISPLAY \
--volume="$HOME/.Xauthority:/root/.Xauthority:rw" \
--volume="/tmp/.X11-unix:/tmp/.X11-unix" \
-p 8888:8888 \
-it alialkadhim/pascal \
/bin/bash

and do bash RUN_dca_int2e-linux-gui.sh to run it.

Here are what the options do

• -v /media/ali/DATA/DATABASE/DCA:/home/shared mounts the directory /media/ali/DATA/DATABASE/DCA on your local machine (host) to /home/shared on the Docker container (client).

• -p 8000:8000 maps the 8000 port of your container to the 8000 port of your real server (the host network), i.e. publishes this port.

  • The order of ports in the p flag is -p <host-port>:<container-port>.

• -it alialkadhim/dca_int2r:linux-gui run the alialkadhim/dca_int2r:linux-gui docker image (username alialkadhim , image name: dca_int2r , tag: linux-gui ) wich can be found on dockerhub here, interactively (meaning when you run it your shell will be inside the docker container).

  • -i for interactive. -t is for enabling a terminal typing interface (i.e. it makes sure the container will work as a terminal would)

⁠Features (potentially) to be implemented in the next version

⁠1. The option to run the docker queries non-interactively - i.e. a user will be able to do a simple query anywhere in the local machine terminal. (The issue here is the tunnel command, I'll have to figure out a way that the lxplus password can be passed to docker run securely without having to type it).  

⁠2. Containerize the queries into functions with one variable, the sensor ID. The user can run the program from anywhere in the terminal by calling e.g.

docker run pascal whereis Sensor ID 24958

But allow the possibility for people to enter SQL commands themselves. These commands can be inserted in the command line or called on by typing the command in .txtor.sql` file.

⁠3. Get input from HGCAL community on what data they would like to retrieve -> containarize into .sql templates. E.g. HPK data, tables other than Si sensors.

⁠4. Make the feature to expert the results of the query into a csv file or a json file. Maybe also ROOT, Yaml

⁠5. Make "query helpers" being a GUI query-builder

⁠6. See if I can use OpenShift⁠ for it somehow. I think this thing should be a web application hosted on [[PaaS or OpenShift]]. Or API. The application would be a browser, s SQL query, or a SQL query helper with voice, etc. It will also have the possibility of just starting a jupyter notebook.

⁠7. Incorporate AI speech recognition feature like AI assistant that you can talk to. e.g. https://www.ai2sql.io/⁠

Example: saying

Which would be translated to the correct SQL command and executed with output.

⁠8. Make the use of the program improve the program itself, especially for your needs, such that it learns from your queries, like a recommender system. such that the AI tool is able to self-correct its prediction for what you want.

Related:

⁠9. More help for particular queries that you type support autocompletion by tab.

⁠10. more support arm64 architectures. (it already works but it's not arm-based)

⁠11. have one page on readthedocs be my jupyter notebook, maybe it can be opened in Swan like Olia suggested. It will have a button (open in swan).

⁠12. Support uploading results as well with dbloader.

⁠13. hgcweb?

⁠X forwarding-related flags

see

• https://www.howtogeek.com/devops/how-to-run-gui-applications-in-a-docker-container/⁠
• http://fabiorehm.com/blog/2014/09/11/running-gui-apps-with-docker/⁠ Accessing the X server requires 2 things:

1. The $DISPLAY variable pointing to the correct display (usually set to :0)
2. Proper authentication information ($XAUTHORITY or xauth)
   • $XAUTHORITY should point to ~/.Xauthority.
     • In Debian, if this environemnt variable is not set, just do export XAUTHORITY=~/.Xauthority
   • also do xauth list to find authentication information.

You might need to install xauth so that the container can access the X server. On your host machine do

xauth list | grep `uname -n`

and copy the hex key. Then add this display with

xauth add $DISPLAY . hexkey

• --volume="/tmp/.X11-unix:/tmp/.X11-unix" is a bind mount of the X server directory to the container.

• --volume="$HOME/.Xauthority:/root/.Xauthority:rw" is providing your host's X socket to the docker container - it gives the container the required credentials .The X socket can be found in /tmp/.X11-unix on your host.

• --env="DISPLAY" is providing the container with a DISPLAY environment variable (so that a gui, i.e. an X client, can can connect to the X server.)

  • Do echo $DISPLAY to see
  • We are setting the DISPLAY variable in the container to the value of $DISPLAY on the host. This is usually set to :0. The number is just to distinguish with different displays. For example the socket file /etc/.X11-unix/X0 or /tmp/.X11-unix/X0 uses the :0 display.
  • -e DISPLAY=$DISPLAY is equivalent to --env="DISPLAY"
• • --net=host makes the programs inside the docker container look like they are running on the host itself, from the perspective of the network.

⁠A bit of discussion of what programs are installed in the docker:

• oracle-instantClient
• python3.7
• sqlite3
• cx-Oracle==7.0.0 which uses the installed oracle-instantClient with their versions corresponding to those that host int2r. cx_oracle documentation: https://cx-oracle.readthedocs.io/⁠
• jupyter-lab
• a bunch of base software like wget, git, openssl, etc. The Oracle Instant Client libraries provide the necessary network connectivity, as well as Oracle Database client-side files to create and run Oracle Call Interface (OCI), Oracle C++ Call Interface (OCCI), ODBC, and JDBC OCI applications to make full use of Oracle Database.

Installation instructions: https://docs.oracle.com/en/database/oracle/oracle-database/21/lacli/install-instant-client-using-zip.html#GUID-D3DCB4FB-D3CA-4C25-BE48-3A1FB5A22E84⁠

⁠A bit of discussion of what files are inside the docker

After you docker pull and docker run -it ... as described above, your working directory is /home. Do

tree

• Tutorials (Docker, Django, SQL, Docker run, ssh)
• Other docs/tutorials: I don't want to overwhelm the user with many docs, so I only include a handful that I think are the most useful to the user. These are:
  • Aivarus presentation
  • My presentation from Dec. 13 2022
  • Umesh document on DB layout/templates
• tunnel.sh
• tns.ora
• Pascal_Tutorial1_V1.ipynb

⁠A Quick Tutorial on SQL Queries for HGCAL Database; Most of These are Included in the Pascal Docker Image

Currently a few basic ones are included based on what should be included by Alex and Ludivine.

You should think of the CMS_HGC_CORE_COND.COND_DATA_SETS table as the master table that has a record of everything that was uploaded, and the CONDITION_DATA_SET_ID as the successful upload ID that relates schemas and tables.

• See all attempts to upload and their success/failure status and their logs. Replace "Ali" with the name of the user who aploaded something, and you should see it, whether it was uploaded successfully and a log file associated with it!

select * from CMS_HGC_CORE_MANAGEMNT.CONDITIONS_DATA_AUDITLOG where RECORD_LASTUPDATE_USER LIKE 'Ali%';

• See Everything uploaded by Chaochen:

- QUERY_EVA="select * from CMS_HGC_CORE_MANAGEMNT.CONDITIONS_DATA_AUDITLOG where RECORD_LASTUPDATE_USER LIKE 'Chao%'"
execute_query(QUERY_EVA)

• See any upload to the database by the user whose name starts with "Ali". Explanation: the CONDITIONS_DATA_AUDITLOG has a record and log file of uploads/attempt to upload. Replace "Ali" with the name of the user who aploaded something, and you should see it, whether it was uploaded successfully and a log file associated with it!

select * from CMS_HGC_CORE_MANAGEMNT.CONDITIONS_DATA_AUDITLOG where RECORD_LASTUPDATE_USER LIKE 'Ali%';

• See the names of all the tables in the CMS_HGC_HGCAL_COND account (and the number of rows in each)

select table_name, num_rows
from all_tables
  where owner='CMS_HGC_HGCAL_COND';

• see everythong in the hcg_cern_sensor_ivl table:

select * from CMS_HGC_HGCAL_COND.hgc_cern_sensor_iv;

• See the uploaded registered parts (wafers), ordered by the time they were uploaded to the database:

select * from CMS_HGC_CORE_CONSTRUCT.PARTS order by RECORD_INSERTION_TIME ASC;

• See the registered wafer that has serial number "100113":

select * from CMS_HGC_CORE_CONSTRUCT.PARTS where SERIAL_NUMBER='100113'; 

• See the uploaded wafer kind of part ID that was uploaded by the user "Alex%" (i.e. it matches any user name that starts with "Alex").

select  KIND_OF_PART_ID, NAME_LABEL
from CMS_HGC_CORE_CONSTRUCT.PARTS Where RECORD_INSERTION_USER LIKE 'Alex%';

For the HGCAL data we sometimes need to use some Inner join commands in our SQL query. Basically inner join lets you join your initial table with another table, at a particular field that is the same in both tables.

• stupid way to see the first CV table that I uploaded.

select * from CMS_HGC_HGCAL_COND.HGC_CERN_SENSOR_CV
INNER JOIN CMS_HGC_CORE_COND.COND_DATA_SETS
ON CMS_HGC_HGCAL_COND.HGC_CERN_SENSOR_CV.CONDITION_DATA_SET_ID = CMS_HGC_CORE_COND.COND_DATA_SETS.CONDITION_DATA_SET_ID
where CMS_HGC_CORE_COND.COND_DATA_SETS.RECORD_INSERTION_USER LIKE '%Ali%'
ORDER BY CELL_NR;