All Products
Search

This Product

    MaxCompute:Use a PyODPS node to query data based on specific criteria

    Document Center

    MaxCompute:Use a PyODPS node to query data based on specific criteria

    Last Updated:Nov 08, 2023

    This topic describes how to use a PyODPS node to query data based on specific criteria.

    Prerequisites

    The following operations are performed:

    • MaxCompute is activated. For more information, see Activate MaxCompute.

    • DataWorks is activated. For more information, see Purchase guide.

    • A workflow is created in the DataWorks console. In this example, a workflow is created for a DataWorks workspace in basic mode. For more information, see Create a workflow.

    Procedure

    1. Create a table and import data to the table.

      1. Download the dataset iris.data from iris and rename iris.data as iris.csv.

      2. Create a table named pyodps_iris and upload the dataset iris.csv to the table. For more information, see Create tables and upload data.

        Sample statement: 

        CREATE TABLE if not exists pyodps_iris
(
sepallength  DOUBLE comment 'sepal length (cm)',
sepalwidth   DOUBLE comment 'sepal width (cm)',
petallength  DOUBLE comment ''petal length (cm)',
petalwidth   DOUBLE comment 'petal width (cm)',
name         STRING comment 'type'
);
    2. Log on to the DataWorks console.

    3. In the left-side navigation pane, click Workspaces.

    4. Find your workspace, and choose Shortcuts > Data Development in the Actions column.

    5. On the DataStudio page, right-click the created workflow and choose Create Node > MaxCompute > PyODPS 2.

    6. In the Create Node dialog box, specify Name and click Confirm.

    7. On the configuration tab of the node, enter sample code in the code editor. 

      import sys
reload(sys)
# Change the default encoding format. 
sys.setdefaultencoding('utf8')

iris = DataFrame(o.get_table('pyodps_iris'))
# Method 1: Return the result based on a conditional query statement. 
with o.execute_sql('select * from pyodps_iris WHERE sepallength > 5 ').open_reader() as reader4:
    print reader4.raw
    for record in reader4:
        print record["sepallength"],record["sepalwidth"],record["petallength"],record["petalwidth"],record["name"]

# Method 2: Return the result based on the filter conditions of PyODPS DataFrame. 
print iris[iris.sepallength > 5].head(5)

# Method 3: Return the result by using the query method of PyODPS DataFrame. 
print iris.query("(sepallength < 5) and (petallength > 1.5)").head(5)

    8. Click 运行.

      单击运行按钮

    9. View the execution result of the PyODPS 2 node on the Runtime Log tab. 

      Executing user script with PyODPS 0.8.0

"sepallength","sepalwidth","petallength","petalwidth","name"
5.1,3.5,1.4,0.2,"Iris-setosa"
5.4,3.9,1.7,0.4,"Iris-setosa"
5.4,3.7,1.5,0.2,"Iris-setosa"
5.8,4.0,1.2,0.2,"Iris-setosa"
5.7,4.4,1.5,0.4,"Iris-setosa"
5.4,3.9,1.3,0.4,"Iris-setosa"
5.1,3.5,1.4,0.3,"Iris-setosa"
5.7,3.8,1.7,0.3,"Iris-setosa"
5.1,3.8,1.5,0.3,"Iris-setosa"
5.4,3.4,1.7,0.2,"Iris-setosa"
5.1,3.7,1.5,0.4,"Iris-setosa"
5.1,3.3,1.7,0.5,"Iris-setosa"
5.2,3.5,1.5,0.2,"Iris-setosa"
5.2,3.4,1.4,0.2,"Iris-setosa"
5.4,3.4,1.5,0.4,"Iris-setosa"
5.2,4.1,1.5,0.1,"Iris-setosa"
5.5,4.2,1.4,0.2,"Iris-setosa"
5.5,3.5,1.3,0.2,"Iris-setosa"
5.1,3.4,1.5,0.2,"Iris-setosa"
5.1,3.8,1.9,0.4,"Iris-setosa"
5.1,3.8,1.6,0.2,"Iris-setosa"
5.3,3.7,1.5,0.2,"Iris-setosa"
7.0,3.2,4.7,1.4,"Iris-versicolor"
6.4,3.2,4.5,1.5,"Iris-versicolor"
6.9,3.1,4.9,1.5,"Iris-versicolor"
5.5,2.3,4.0,1.3,"Iris-versicolor"
6.5,2.8,4.6,1.5,"Iris-versicolor"
5.7,2.8,4.5,1.3,"Iris-versicolor"
6.3,3.3,4.7,1.6,"Iris-versicolor"
6.6,2.9,4.6,1.3,"Iris-versicolor"
5.2,2.7,3.9,1.4,"Iris-versicolor"
5.9,3.0,4.2,1.5,"Iris-versicolor"
6.0,2.2,4.0,1.0,"Iris-versicolor"
6.1,2.9,4.7,1.4,"Iris-versicolor"
5.6,2.9,3.6,1.3,"Iris-versicolor"
6.7,3.1,4.4,1.4,"Iris-versicolor"
5.6,3.0,4.5,1.5,"Iris-versicolor"
5.8,2.7,4.1,1.0,"Iris-versicolor"
6.2,2.2,4.5,1.5,"Iris-versicolor"
5.6,2.5,3.9,1.1,"Iris-versicolor"
5.9,3.2,4.8,1.8,"Iris-versicolor"
6.1,2.8,4.0,1.3,"Iris-versicolor"
6.3,2.5,4.9,1.5,"Iris-versicolor"
6.1,2.8,4.7,1.2,"Iris-versicolor"
6.4,2.9,4.3,1.3,"Iris-versicolor"
6.6,3.0,4.4,1.4,"Iris-versicolor"
6.8,2.8,4.8,1.4,"Iris-versicolor"
6.7,3.0,5.0,1.7,"Iris-versicolor"
6.0,2.9,4.5,1.5,"Iris-versicolor"
5.7,2.6,3.5,1.0,"Iris-versicolor"
5.5,2.4,3.8,1.1,"Iris-versicolor"
5.5,2.4,3.7,1.0,"Iris-versicolor"
5.8,2.7,3.9,1.2,"Iris-versicolor"
6.0,2.7,5.1,1.6,"Iris-versicolor"
5.4,3.0,4.5,1.5,"Iris-versicolor"
6.0,3.4,4.5,1.6,"Iris-versicolor"
6.7,3.1,4.7,1.5,"Iris-versicolor"
6.3,2.3,4.4,1.3,"Iris-versicolor"
5.6,3.0,4.1,1.3,"Iris-versicolor"
5.5,2.5,4.0,1.3,"Iris-versicolor"
5.5,2.6,4.4,1.2,"Iris-versicolor"
6.1,3.0,4.6,1.4,"Iris-versicolor"
5.8,2.6,4.0,1.2,"Iris-versicolor"
5.6,2.7,4.2,1.3,"Iris-versicolor"
5.7,3.0,4.2,1.2,"Iris-versicolor"
5.7,2.9,4.2,1.3,"Iris-versicolor"
6.2,2.9,4.3,1.3,"Iris-versicolor"
5.1,2.5,3.0,1.1,"Iris-versicolor"
5.7,2.8,4.1,1.3,"Iris-versicolor"
6.3,3.3,6.0,2.5,"Iris-virginica"
5.8,2.7,5.1,1.9,"Iris-virginica"
7.1,3.0,5.9,2.1,"Iris-virginica"
6.3,2.9,5.6,1.8,"Iris-virginica"
6.5,3.0,5.8,2.2,"Iris-virginica"
7.6,3.0,6.6,2.1,"Iris-virginica"
7.3,2.9,6.3,1.8,"Iris-virginica"
6.7,2.5,5.8,1.8,"Iris-virginica"
7.2,3.6,6.1,2.5,"Iris-virginica"
6.5,3.2,5.1,2.0,"Iris-virginica"
6.4,2.7,5.3,1.9,"Iris-virginica"
6.8,3.0,5.5,2.1,"Iris-virginica"
5.7,2.5,5.0,2.0,"Iris-virginica"
5.8,2.8,5.1,2.4,"Iris-virginica"
6.4,3.2,5.3,2.3,"Iris-virginica"
6.5,3.0,5.5,1.8,"Iris-virginica"
7.7,3.8,6.7,2.2,"Iris-virginica"
7.7,2.6,6.9,2.3,"Iris-virginica"
6.0,2.2,5.0,1.5,"Iris-virginica"
6.9,3.2,5.7,2.3,"Iris-virginica"
5.6,2.8,4.9,2.0,"Iris-virginica"
7.7,2.8,6.7,2.0,"Iris-virginica"
6.3,2.7,4.9,1.8,"Iris-virginica"
6.7,3.3,5.7,2.1,"Iris-virginica"
7.2,3.2,6.0,1.8,"Iris-virginica"
6.2,2.8,4.8,1.8,"Iris-virginica"
6.1,3.0,4.9,1.8,"Iris-virginica"
6.4,2.8,5.6,2.1,"Iris-virginica"
7.2,3.0,5.8,1.6,"Iris-virginica"
7.4,2.8,6.1,1.9,"Iris-virginica"
7.9,3.8,6.4,2.0,"Iris-virginica"
6.4,2.8,5.6,2.2,"Iris-virginica"
6.3,2.8,5.1,1.5,"Iris-virginica"
6.1,2.6,5.6,1.4,"Iris-virginica"
7.7,3.0,6.1,2.3,"Iris-virginica"
6.3,3.4,5.6,2.4,"Iris-virginica"
6.4,3.1,5.5,1.8,"Iris-virginica"
6.0,3.0,4.8,1.8,"Iris-virginica"
6.9,3.1,5.4,2.1,"Iris-virginica"
6.7,3.1,5.6,2.4,"Iris-virginica"
6.9,3.1,5.1,2.3,"Iris-virginica"
5.8,2.7,5.1,1.9,"Iris-virginica"
6.8,3.2,5.9,2.3,"Iris-virginica"
6.7,3.3,5.7,2.5,"Iris-virginica"
6.7,3.0,5.2,2.3,"Iris-virginica"
6.3,2.5,5.0,1.9,"Iris-virginica"
6.5,3.0,5.2,2.0,"Iris-virginica"
6.2,3.4,5.4,2.3,"Iris-virginica"
5.9,3.0,5.1,1.8,"Iris-virginica"
5.1 3.5 1.4 0.2 Iris-setosa
5.4 3.9 1.7 0.4 Iris-setosa
5.4 3.7 1.5 0.2 Iris-setosa
5.8 4.0 1.2 0.2 Iris-setosa
5.7 4.4 1.5 0.4 Iris-setosa
5.4 3.9 1.3 0.4 Iris-setosa
5.1 3.5 1.4 0.3 Iris-setosa
5.7 3.8 1.7 0.3 Iris-setosa
5.1 3.8 1.5 0.3 Iris-setosa
5.4 3.4 1.7 0.2 Iris-setosa
5.1 3.7 1.5 0.4 Iris-setosa
5.1 3.3 1.7 0.5 Iris-setosa
5.2 3.5 1.5 0.2 Iris-setosa
5.2 3.4 1.4 0.2 Iris-setosa
5.4 3.4 1.5 0.4 Iris-setosa
5.2 4.1 1.5 0.1 Iris-setosa
5.5 4.2 1.4 0.2 Iris-setosa
5.5 3.5 1.3 0.2 Iris-setosa
5.1 3.4 1.5 0.2 Iris-setosa
5.1 3.8 1.9 0.4 Iris-setosa
5.1 3.8 1.6 0.2 Iris-setosa
5.3 3.7 1.5 0.2 Iris-setosa
7.0 3.2 4.7 1.4 Iris-versicolor
6.4 3.2 4.5 1.5 Iris-versicolor
6.9 3.1 4.9 1.5 Iris-versicolor
5.5 2.3 4.0 1.3 Iris-versicolor
6.5 2.8 4.6 1.5 Iris-versicolor
5.7 2.8 4.5 1.3 Iris-versicolor
6.3 3.3 4.7 1.6 Iris-versicolor
6.6 2.9 4.6 1.3 Iris-versicolor
5.2 2.7 3.9 1.4 Iris-versicolor
5.9 3.0 4.2 1.5 Iris-versicolor
6.0 2.2 4.0 1.0 Iris-versicolor
6.1 2.9 4.7 1.4 Iris-versicolor
5.6 2.9 3.6 1.3 Iris-versicolor
6.7 3.1 4.4 1.4 Iris-versicolor
5.6 3.0 4.5 1.5 Iris-versicolor
5.8 2.7 4.1 1.0 Iris-versicolor
6.2 2.2 4.5 1.5 Iris-versicolor
5.6 2.5 3.9 1.1 Iris-versicolor
5.9 3.2 4.8 1.8 Iris-versicolor
6.1 2.8 4.0 1.3 Iris-versicolor
6.3 2.5 4.9 1.5 Iris-versicolor
6.1 2.8 4.7 1.2 Iris-versicolor
6.4 2.9 4.3 1.3 Iris-versicolor
6.6 3.0 4.4 1.4 Iris-versicolor
6.8 2.8 4.8 1.4 Iris-versicolor
6.7 3.0 5.0 1.7 Iris-versicolor
6.0 2.9 4.5 1.5 Iris-versicolor
5.7 2.6 3.5 1.0 Iris-versicolor
5.5 2.4 3.8 1.1 Iris-versicolor
5.5 2.4 3.7 1.0 Iris-versicolor
5.8 2.7 3.9 1.2 Iris-versicolor
6.0 2.7 5.1 1.6 Iris-versicolor
5.4 3.0 4.5 1.5 Iris-versicolor
6.0 3.4 4.5 1.6 Iris-versicolor
6.7 3.1 4.7 1.5 Iris-versicolor
6.3 2.3 4.4 1.3 Iris-versicolor
5.6 3.0 4.1 1.3 Iris-versicolor
5.5 2.5 4.0 1.3 Iris-versicolor
5.5 2.6 4.4 1.2 Iris-versicolor
6.1 3.0 4.6 1.4 Iris-versicolor
5.8 2.6 4.0 1.2 Iris-versicolor
5.6 2.7 4.2 1.3 Iris-versicolor
5.7 3.0 4.2 1.2 Iris-versicolor
5.7 2.9 4.2 1.3 Iris-versicolor
6.2 2.9 4.3 1.3 Iris-versicolor
5.1 2.5 3.0 1.1 Iris-versicolor
5.7 2.8 4.1 1.3 Iris-versicolor
6.3 3.3 6.0 2.5 Iris-virginica
5.8 2.7 5.1 1.9 Iris-virginica
7.1 3.0 5.9 2.1 Iris-virginica
6.3 2.9 5.6 1.8 Iris-virginica
6.5 3.0 5.8 2.2 Iris-virginica
7.6 3.0 6.6 2.1 Iris-virginica
7.3 2.9 6.3 1.8 Iris-virginica
6.7 2.5 5.8 1.8 Iris-virginica
7.2 3.6 6.1 2.5 Iris-virginica
6.5 3.2 5.1 2.0 Iris-virginica
6.4 2.7 5.3 1.9 Iris-virginica
6.8 3.0 5.5 2.1 Iris-virginica
5.7 2.5 5.0 2.0 Iris-virginica
5.8 2.8 5.1 2.4 Iris-virginica
6.4 3.2 5.3 2.3 Iris-virginica
6.5 3.0 5.5 1.8 Iris-virginica
7.7 3.8 6.7 2.2 Iris-virginica
7.7 2.6 6.9 2.3 Iris-virginica
6.0 2.2 5.0 1.5 Iris-virginica
6.9 3.2 5.7 2.3 Iris-virginica
5.6 2.8 4.9 2.0 Iris-virginica
7.7 2.8 6.7 2.0 Iris-virginica
6.3 2.7 4.9 1.8 Iris-virginica
6.7 3.3 5.7 2.1 Iris-virginica
7.2 3.2 6.0 1.8 Iris-virginica
6.2 2.8 4.8 1.8 Iris-virginica
6.1 3.0 4.9 1.8 Iris-virginica
6.4 2.8 5.6 2.1 Iris-virginica
7.2 3.0 5.8 1.6 Iris-virginica
7.4 2.8 6.1 1.9 Iris-virginica
7.9 3.8 6.4 2.0 Iris-virginica
6.4 2.8 5.6 2.2 Iris-virginica
6.3 2.8 5.1 1.5 Iris-virginica
6.1 2.6 5.6 1.4 Iris-virginica
7.7 3.0 6.1 2.3 Iris-virginica
6.3 3.4 5.6 2.4 Iris-virginica
6.4 3.1 5.5 1.8 Iris-virginica
6.0 3.0 4.8 1.8 Iris-virginica
6.9 3.1 5.4 2.1 Iris-virginica
6.7 3.1 5.6 2.4 Iris-virginica
6.9 3.1 5.1 2.3 Iris-virginica
5.8 2.7 5.1 1.9 Iris-virginica
6.8 3.2 5.9 2.3 Iris-virginica
6.7 3.3 5.7 2.5 Iris-virginica
6.7 3.0 5.2 2.3 Iris-virginica
6.3 2.5 5.0 1.9 Iris-virginica
6.5 3.0 5.2 2.0 Iris-virginica
6.2 3.4 5.4 2.3 Iris-virginica
5.9 3.0 5.1 1.8 Iris-virginica
Sql compiled:
CREATE TABLE tmp_pyodps_xxxx LIFECYCLE 1 AS
SELECT *
FROM xxx.`pyodps_iris` t1
WHERE t1.`sepallength` > 5
Instance ID: 2019xxxx
  Log view: http://logview.odps.aliyun.com/logview/?h=http://service.cn.maxcompute.aliyun.com/api&p=xxxx&i=2019xxxx&token=xxxx

   sepallength  sepalwidth  petallength  petalwidth         name
0          5.1         3.5          1.4         0.2  Iris-setosa
1          5.4         3.9          1.7         0.4  Iris-setosa
2          5.4         3.7          1.5         0.2  Iris-setosa
3          5.8         4.0          1.2         0.2  Iris-setosa
4          5.7         4.4          1.5         0.4  Iris-setosa
Sql compiled:
CREATE TABLE tmp_pyodps_xxxx LIFECYCLE 1 AS
SELECT *
FROM xxxx.`pyodps_iris` t1
WHERE (t1.`sepallength` < 5) AND (t1.`petallength` > 1.5)
Instance ID: 2019xxxx
  Log view: http://logview.odps.aliyun.com/logview/?h=http://service.cn.maxcompute.aliyun.com/api&p=xxxx&i=2019xxxx&token=xxxx

   sepallength  sepalwidth  petallength  petalwidth             name
0          4.8         3.4          1.6         0.2      Iris-setosa
1          4.8         3.4          1.9         0.2      Iris-setosa
2          4.7         3.2          1.6         0.2      Iris-setosa
3          4.8         3.1          1.6         0.2      Iris-setosa
4          4.9         2.4          3.3         1.0  Iris-versicolor