User can access actual columns and path variables by creating a view using a SELECT query with each column from JSON or CSV data projected from foreign table. Each column must be typecast to a valid type and then aliased to the appropriate column name. This allows user to access actual columns and keys in the JSON or CSV data. It is up to the user on what must be selected in SELECT query passed to "DataFrame.from_query()": columns, attributes, keys from JSON or CSV data and path variables.
Example for JSON data
Create a view.
# While creating a view select each column is type casted to a valid type and # then aliased to the required column name. Notice, we are selecting each attribute including path variables. # Following is the VIEW created at the backend: """ REPLACE VIEW riverflowview AS ( SELECT CAST($path.$siteno AS CHAR(10)) TheSite, CAST($path.$year AS CHAR(4)) TheYear, CAST($path.$month AS CHAR(2)) TheMonth, CAST($path.$day AS CHAR(2)) TheDay, CAST(payload.site_no AS CHAR(8)) Site_no, CAST(payload.Flow AS FLOAT) Flow, CAST(payload.GageHeight AS FLOAT) GageHeight1, CAST(payload.Precipitation AS FLOAT) Precipitation, CAST(payload.Temp AS FLOAT) Temperature, CAST(payload.Velocity AS FLOAT) Velocity, CAST(payload.BatteryVoltage AS FLOAT) BatteryVoltage, CAST(payload.GageHeight2 AS FLOAT) GageHeight2 FROM riverflow); """
Create a DataFrame on the view and display the head of the DataFrame.
# Create a DataFrame on a view. >>> wrk2dfview = DataFrame("riverflowview") >>> wrk2dfview.head().to_pandas()
TheSite TheYear TheMonth TheDay Site_no Flow GageHeight1 Precipitation Temperature Velocity BatteryVoltage GageHeight2 0 09380000 2018 07 17 09380000 18300.0 10.36 0.0 12.0 None None None 1 09380000 2018 07 11 09380000 18000.0 10.31 0.0 11.2 None None None 2 09380000 2018 07 11 09380000 18500.0 10.40 0.0 11.5 None None None 3 09380000 2018 07 06 09380000 19000.0 10.47 0.0 11.5 None None None 4 09380000 2018 07 14 09380000 11500.0 9.01 0.0 10.3 None None None 5 09380000 2018 07 04 09380000 11400.0 8.98 0.0 11.9 None None None 6 09380000 2018 07 01 09380000 11100.0 8.92 0.0 10.7 None None None 7 09380000 2018 06 29 09380000 16700.0 10.07 0.0 11.3 None None None 8 09380000 2018 07 25 09380000 18100.0 10.33 0.0 11.9 None None None 9 09380000 2018 07 02 09380000 18800.0 10.44 0.0 11.4 None None None
Example for CSV data
Create a view.
# While creating a view select each column is type casted to a valid type and # then aliased to the required column name. Notice, we are selecting each attribute including path variables. # Following is the VIEW created at the backend: """ REPLACE VIEW riverflowcsvview AS ( SELECT CAST($path.$siteno AS CHAR(10)) TheSite, CAST($path.$year AS CHAR(4)) TheYear, CAST($path.$month AS CHAR(2)) TheMonth, CAST($path.$day AS CHAR(2)) TheDay, CAST(payload..site_no AS CHAR(8)) Site_no, CAST(payload..Flow AS FLOAT) Flow, CAST(payload..GageHeight AS FLOAT) GageHeight1, CAST(payload..Precipitation AS FLOAT) Precipitation, CAST(payload..Temp AS FLOAT) Temperature, CAST(payload..Velocity AS FLOAT) Velocity, CAST(payload..BatteryVoltage AS FLOAT) BatteryVoltage, CAST(payload..GageHeight2 AS FLOAT) GageHeight2 FROM riverflowcsv); """
Create DataFrame on the view and display the head of the DataFrame.
# Create a DataFrame on a view. >>> wrk2dfview = DataFrame("riverflowcsvview") >>> wrk2dfview.head().to_pandas()
TheSite TheYear TheMonth TheDay Site_no Flow GageHeight1 Precipitation Temperature Velocity BatteryVoltage GageHeight2 0 09380000 2018 07 18 09380000 18700.0 10.43 0.0 11.4 None None None 1 09380000 2018 06 29 09380000 16800.0 10.09 0.0 11.0 None None None 2 09380000 2018 07 06 09380000 11100.0 8.90 0.0 10.5 None None None 3 09380000 2018 07 10 09380000 15900.0 9.92 0.0 11.0 None None None 4 09380000 2018 07 12 09380000 18400.0 10.37 0.0 11.2 None None None 5 09380000 2018 07 12 09380000 11500.0 9.00 0.0 11.0 None None None 6 09380000 2018 06 28 09380000 11500.0 9.00 0.0 10.5 None None None 7 09380000 2018 07 12 09380000 11000.0 8.89 0.0 11.0 None None None 8 09380000 2018 07 10 09380000 15400.0 9.82 0.0 10.5 None None None 9 09380000 2018 07 26 09380000 18800.0 10.45 0.0 11.6 None None None