CODEWITHSUNDEEP
sqlhadoopimpala
Dinesh Deenu
Dinesh Deenu

Reputation: 1

Bad status request on Impala

Bad status for request 37563: TGetOperationStatusResp(status=TStatus(errorCode=None, errorMessage=None, sqlState=None, infoMessages=None, statusCode=0), operationState=5, errorMessage=None, sqlState=None, errorCode=None)

I am facing this when i tried to run the following query.

refresh sst_spot_quotes;
select
  case
    when maxrecordforSST >= hours_sub (CURRENT_TIMESTAMP(), 4) then ''
    else 'error'
  end as msg
from
  (
    select
      to_timestamp (
        CONCAT (
          max(rdate),
          MAX(
            CONCAT (
              LPAD (CAST(rhour as STRING), 2, '0'),
              ':',
              LPAD (CAST(rminute as STRING), 2, '0'),
              ':',
              LPAD (CAST(rsecond as STRING), 2, '0')
            )
          )
        ),
        'yyyy-mmm-ddhh:mm:ss'
      ) as maxrecordforSST
    from
      sst_spot_quotes
    where
      rdate >= days_sub (to_date (CURRENT_TIMESTAMP()), 1)
  ) a

Could someone help to resolve this error.

Upvotes: 0

Views: 114

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

The date format must be yyyy-MMM-ddhh:mm:ss. Pls note i made MMM as uppercase. Could you please change and try?

Also, its not a good idea to get the max and then to_timestamp but reverse. Because calculating max on date will give correct maximum date than string.

You can give below SQL a try.

select
  case
    when maxrecordforSST >= hours_sub (CURRENT_TIMESTAMP(), 4) then ''
    else 'error'
  end as msg
from
  (
    select
      MAX(to_timestamp (
        CONCAT (
          (rdate),
          (
            CONCAT (
              LPAD (CAST(rhour as STRING), 2, '0'),
              ':',
              LPAD (CAST(rminute as STRING), 2, '0'),
              ':',
              LPAD (CAST(rsecond as STRING), 2, '0')
            )
          )
        ),
        'yyyy-MMM-ddhh:mm:ss'
      )) as maxrecordforSST
    from
      sst_spot_quotes
    where
      rdate >= days_sub (to_date (CURRENT_TIMESTAMP()), 1)
  ) a

Upvotes: 0

Related Questions